Home » Server Options » Text & interMedia » How to determine last tiime Content was indexed (Oracle 19c)
| How to determine last tiime Content was indexed [message #688015] |
Thu, 10 August 2023 17:12  |
 |
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I have several questions. I hope it is OK to group them together here.
1. How do I determine what values were indexed for a given document? I tried this but zero records were returned.
SELECT TOKEN_TEXT
FROM DR$IMT_MSTR_INDX_URL1$I T
, MASTER_INDEX X
WHERE X.ROWID = T.ROWID
AND X.ASSIGNED_ID = 'MCP-135'
3. Is there a simple way to force Oracle to reindex the content of a document?
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688016 is a reply to message #688015] |
Thu, 10 August 2023 19:05  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Please see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(4000))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO test_tab (id, document)
3 VALUES (111, 'How much wood would a woodchuck chuck if a woodchuck could chuck wood?')
4 INTO test_tab (id, document)
5 VALUES (222, 'Peter Piper picked a peck of pickled peppers.')
6 INTO test_tab (id, document)
7 VALUES (333, 'Does a woodchuck like pickled peppers?')
8 INTO test_tab (id, document)
9 VALUES (444, 'I like pickled peppers')
10 SELECT * FROM DUAL
11 /
4 rows created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX test_idx ON test_tab (document)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('SYNC (ON COMMIT)')
4 /
Index created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO test_tab (id, document)
3 VALUES (555, 'rubber baby buggy bumpers')
4 SELECT * FROM DUAL
5 /
1 row created.
SCOTT@orcl_12.1.0.2.0> COMMIT
2 /
Commit complete.
1. How do I determine what values were indexed for a given document?
The following is about as close as you can get within reason,
because there is no exact match for dr$test_idx$k.docid in dr$test_idx$i,
just a dr$test_idx$i.token_first and dr$test_idx$i.token_last.
Additional data is contained in blob columns that are not readily accessible.
SCOTT@orcl_12.1.0.2.0> SELECT t.id, i.token_text
2 FROM test_tab t, dr$test_idx$k k, dr$test_idx$i i
3 WHERE CONTAINS (t.document, i.token_text) > 0
4 AND t.ROWID = k.textkey
5 AND k.docid BETWEEN i.token_first AND i.token_last
6 -- AND t.id = '111' or whatever id you want to limit the result set to
7 ORDER BY id, token_text
8 /
ID TOKEN_TEXT
---------- ------------------------------
111 CHUCK
111 MUCH
111 WOOD
111 WOODCHUCK
222 PECK
222 PEPPERS
222 PETER
222 PICKED
222 PICKLED
222 PIPER
333 LIKE
333 PEPPERS
333 PICKLED
333 WOODCHUCK
444 LIKE
444 PEPPERS
444 PICKLED
555 BABY
555 BUGGY
555 BUMPERS
555 RUBBER
21 rows selected.
2. Is there a way to determine when a given document was last indexed?
If you use sync (on commit) as in the example above, it is synchronized on commit.
I don't know a way to determine exactly when that was offhand, but others may know of a way.
3. Is there a simple way to force Oracle to reindex the content of a document?
Not just one single document that I know of.
However, as noted above, if you use sync (on commit) it is synchronized on commit.
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688017 is a reply to message #688016] |
Thu, 10 August 2023 21:20 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Thank you!!!
Just a little information that I should have given you first time and maybe it doesn't make any difference, but I cannot see how to complete my #1 question above.
The document is not stored in the table, it resides on the file server. There is a column in the table called "text_content_url", in that column there is a URL/UNC that points to the document. Here is what my index looks like:
===========================================================================
INDEX DESCRIPTION
===========================================================================
index name: "schemaName"."IDXName_URL1"
index id: 1338
index type: context
base table: "SchemaName"."tableName"
primary key column: ID
text column: TEXT_CONTENT_URL
text column type: VARCHAR2(2000)
language column:
format column:
charset column:
configuration column:
Query Stats Enabled: NO
index option: no $R
status: INDEXED
full optimize token:
full optimize count:
docid count: 2791273
nextid: 2838695
===========================================================================
INDEX OBJECTS
===========================================================================
datastore: URL_DATASTORE
timeout: 10
http_proxy: webcache-2.inel.gov:8080
filter: AUTO_FILTER
timeout: 10
timeout_type: HEURISTIC
section group: HTML_SECTION_GROUP
lexer: BASIC_LEXER
whitespace: _-
wordlist: BASIC_WORDLIST
stemmer: ENGLISH
fuzzy_match: GENERIC
stoplist: BASIC_STOPLIST
storage: BASIC_STORAGE
i_table_clause: tablespace CTXSYS_DATA
k_table_clause: tablespace CTXSYS_DATA
r_table_clause: tablespace CTXSYS_DATA
n_table_clause: tablespace CTXSYS_DATA
i_index_clause: tablespace CTXSYS_DATA
[Updated on: Thu, 10 August 2023 21:23] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688018 is a reply to message #688017] |
Thu, 10 August 2023 23:55 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You should be able to copy and paste the query below and run it.
SELECT TOKEN_TEXT
FROM DR$IMT_MSTR_INDX_URL1$I T
, MASTER_INDEX X
, DR$IMT_MSTR_INDX_URL1$K K
WHERE CONTAINS (X.text_content_url, T.token_text) > 0
AND X.ROWID = K.textkey
AND K.docid BETWEEN T.token_first AND T.token_last
AND X.ASSIGNED_ID = 'MCP-135'
/
Here is a demonstration.
COTT@orcl_12.1.0.2.0> CREATE TABLE master_index
2 (assigned_id VARCHAR2(11),
3 text_content_url VARCHAR2(2000))
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT into master_index (assigned_id, text_content_url)
2 VALUES ('MCP-135', ' http://www.example.com/index.html')
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> BEGIN
2 ctx_ddl.create_preference('URL_PREF','URL_DATASTORE');
3 ctx_ddl.set_attribute('URL_PREF','NO_PROXY','http://www.example.com/index.html');
4 ctx_ddl.set_attribute('URL_PREF','Timeout','10');
5 END;
6 /
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX IMT_MSTR_INDX_URL1 ON master_index (text_content_url)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('DATASTORE url_pref')
4 /
Index created.
SCOTT@orcl_12.1.0.2.0> SELECT TOKEN_TEXT
2 FROM DR$IMT_MSTR_INDX_URL1$I T
3 , MASTER_INDEX X
4 , DR$IMT_MSTR_INDX_URL1$K K
5 WHERE CONTAINS (X.text_content_url, T.token_text) > 0
6 AND X.ROWID = K.textkey
7 AND K.docid BETWEEN T.token_first AND T.token_last
8 AND X.ASSIGNED_ID = 'MCP-135'
9 /
TOKEN_TEXT
------------------------------
ASKING
COORDINATION
DOCUMENTS
DOMAIN
EXAMPLE
EXAMPLES
ILLUSTRATIVE
INFORMATION
LITERATURE
MAY
MORE
PERMISSION
PRIOR
USE
WITHOUT
15 rows selected.
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688024 is a reply to message #688018] |
Mon, 14 August 2023 09:03 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
The query, ran for 10 minutes and then I finally killed it. Not sure why it takes so long.
But maybe this will help you see what I am trying to solve.
I have a document MCP-135 that i am trying to index its content. The document is not stored in the database but on a file server.
Here is what I am trying to do:
INSERT INTO TEST_TOKEN (SELECT * FROM MASTER_INDEX WHERE ID = p_id);
commit;
EXECUTE IMMEDIATE 'ALTER INDEX mi.TEST_TOKEN_COMP1 REBUILD ONLINE';
EXECUTE IMMEDIATE 'ALTER INDEX mi.TEST_TOKEN_URL1 REBUILD ONLINE';
CTX_DDL.OPTIMIZE_INDEX('mi.test_token_comp1', 'FULL');
CTX_DDL.OPTIMIZE_INDEX('mi.test_token_url1', 'FULL');
select distinct token_text
, token_type
, token_count
FROM dr$test_token_url1$I
order by token_text, token_type;
I am not getting all of the tokens. I get a whole bunch but the list is incomplete. For instance the document MCP-135 references other documents like ABC-123, EDF-321, ...
I would have expected to see tokens for thos documents like:
AB
EDF
123
321
But I am not. Is there a way to see if it is erroring out, is there a log to see if it is failing when doing the URL Content index?
The select statement
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688025 is a reply to message #688024] |
Mon, 14 August 2023 13:37 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Here is a little better picture of what I am running.
BEGIN
commit;
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX MI.kjc_test_index_url1 FORCE';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('1. FAILED TO DROP INDEX.'||SQLERRM);
END;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE KJC_TEST_INDEX';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('2. FAILED TO DROP TABLE.'||SQLERRM);
END;
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE KJC_TEST_INDEX
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000))';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('3. FAILED TO CREATE TABLE.'||SQLERRM);
END;
-- Creat basic storage preference.
BEGIN
CTX_DDL.DROP_PREFERENCE('kjc_test_index_storage');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('4. FAILED TO DROP PREFERENCE.'||SQLERRM);
END;
BEGIN
ctx_ddl.create_preference('kjc_test_index_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_TABLE_CLAUSE','tablespace CTXSYS_DATA ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'K_TABLE_CLAUSE','tablespace CTXSYS_DATA ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'R_TABLE_CLAUSE','tablespace CTXSYS_DATA ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'N_TABLE_CLAUSE','tablespace CTXSYS_DATA ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_INDEX_CLAUSE','tablespace CTXSYS_DATA ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('5. FAILED TO SET PREFERENCE FOR STORAGE.'||SQLERRM);
END;
-- CREATING THE LEXER TO ALLOW FOR WHITESPACE FOR _-
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_Lex_URL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('6. FAILED TO DROP PREFERENCE lEX.'||SQLERRM);
END;
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_Lex_URL', 'whitespace', '_-');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('7. FAILED TO SET PREFERENCE FOR LEX.'||SQLERRM);
END;
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_AUTO_FILTER');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('8. FAILED TO DROP PREFERENCE FOR FILTER.'||SQLERRM);
END;
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout','10');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout_type','HEURISTIC');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('9. FAILED TO DROP PREFERENCE FOR FILTER.'||SQLERRM);
END;
-- Creat URL Datastore preference.
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('10. FAILED TO DROP DATASTORE.'||SQLERRM);
END;
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE','URL_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','Timeout','10');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','HTTP_PROXY', 'webcache-2.inel.gov:8080');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('11. FAILED TO SET PREFERENCE FOR DATASTORE.'||SQLERRM);
END;
BEGIN
EXECUTE IMMEDIATE 'INSERT into kjc_test_index (assigned_id, text_content_url)
VALUES (MCP-135, file:///usr/edms/web/edm22h/10160/10160236.docx)';
commit;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('12. FAILED TO INSERT RECORD.'||SQLERRM);
END;
commit;
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX MI.kjc_test_index_url1 ON MI.KJC_TEST_INDEX(TEXT_CONTENT_URL) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(DATASTORE KJC_TEST_INDEX_URL_DATASTORE LEXER KJC_TEST_INDEX_Lex_URL STOPLIST CTXSYS.EMPTY_STOPLIST STORAGE KJC_TEST_INDEX_storage FILTER KJC_TEST_INDEX_AUTO_FILTER MEMORY 50M NOPOPULATE) PARALLEL 5';
END;
END;
And here is the error that I am getting when it gets down to creating the index.
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drixmd.PurgeKGL
ORA-20000: Oracle Text error:
ORA-04021: timeout occurred while waiting to lock object MI.KJC_TEST_INDEX_URL1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRIXMD", line 2664
ORA-00001: unique constraint (CTXSYS.DRC$IDX_COLSPEC) violated
ORA-06512: at "CTXSYS.DRIXMD", line 2639
DRG-50610: internal error: kglpurge []
DRG-10507: duplicate index name: KJC_TEST_INDEX_URL1
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
ORA-06512: at line 107
Any ideas on what I am doing wrong?
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688026 is a reply to message #688025] |
Mon, 14 August 2023 15:03 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You have two separate sets of issues.
The first is just creating a valid index to begin with.
The second, which requires that the first is already done right, is checking your index rebuild progress and errors.
I will address them separately in two different posts.
Your use of "exception when others then" tends to obfuscate errors instead of helping. You have a lot of things that could be done in plain SQL instead of PL/SQL which adds another layer of potential problems. When you try to do DDL in PL/SQL, obviously you have figured out that you need to use execute immediate, but when you do, anywhere that you would have a single quote mark in SQL, you need to have two single quotes marks. So, it is better to eliminate as much of the PL/SQL and execute immediate as possible.
I typically begin a test script with
spool test.txt
then end it with
spool off
ed test.txt
So that when the script is done running, it automatically takes me to view the results in a file.
In the example below, I have corrected your invalid code and eliminated as much PL/SQL as possible. I have also removed any "mi." schema preferences, so that I could run it on my system without creating a new schema and replaced "tablespace CTXSYS_DATA" with "tablespace USERS" so that I would not have to create another tablespace. You may want to put those back.
SPOOL TEST.TXT
commit;
DROP INDEX kjc_test_index_url1 FORCE;
DROP TABLE KJC_TEST_INDEX;
CREATE TABLE KJC_TEST_INDEX
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('kjc_test_index_storage');
END;
/
BEGIN
ctx_ddl.create_preference('kjc_test_index_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout','10');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE','URL_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','Timeout','10');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','HTTP_PROXY', 'webcache-2.inel.gov:8080');
END;
/
INSERT into kjc_test_index (assigned_id, text_content_url)
VALUES ('MCP-135', 'file:///usr/edms/web/edm22h/10160/10160236.docx');
commit;
CREATE INDEX kjc_test_index_url1
ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_URL_DATASTORE
LEXER KJC_TEST_INDEX_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_storage
FILTER KJC_TEST_INDEX_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
PARALLEL 5;
SPOOL OFF
ED TEST.TXT
The following is the resulting test.txt on my system.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> DROP INDEX kjc_test_index_url1 FORCE;
Index dropped.
C##SCOTT@XE_21.3.0.0.0> DROP TABLE KJC_TEST_INDEX;
Table dropped.
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE KJC_TEST_INDEX
2 (assigned_id VARCHAR2(11),
3 text_content_url VARCHAR2(2000));
Table created.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('kjc_test_index_storage');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('kjc_test_index_storage', 'BASIC_STORAGE');
3 ctx_ddl.set_attribute('kjc_test_index_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
4 ctx_ddl.set_attribute('kjc_test_index_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
5 ctx_ddl.set_attribute('kjc_test_index_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
6 ctx_ddl.set_attribute('kjc_test_index_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
7 ctx_ddl.set_attribute('kjc_test_index_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
8 END;
9 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_Lex_URL');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_Lex_URL', 'BASIC_LEXER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_Lex_URL', 'whitespace', '_-');
4 END;
5 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_AUTO_FILTER');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_AUTO_FILTER','AUTO_FILTER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout','10');
4 ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout_type','HEURISTIC');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE','URL_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','Timeout','10');
4 CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','HTTP_PROXY', 'webcache-2.inel.gov:8080');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> INSERT into kjc_test_index (assigned_id, text_content_url)
2 VALUES ('MCP-135', 'file:///usr/edms/web/edm22h/10160/10160236.docx');
1 row created.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> CREATE INDEX kjc_test_index_url1
2 ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS
5 ('DATASTORE KJC_TEST_INDEX_URL_DATASTORE
6 LEXER KJC_TEST_INDEX_Lex_URL
7 STOPLIST CTXSYS.EMPTY_STOPLIST
8 STORAGE KJC_TEST_INDEX_storage
9 FILTER KJC_TEST_INDEX_AUTO_FILTER
10 MEMORY 50M
11 NOPOPULATE')
12 PARALLEL 5;
Index created.
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688027 is a reply to message #688025] |
Mon, 14 August 2023 15:24 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is a simplified demonstration based on your previous post that shows how to check your index creation for errors and how to create a log of the details of the index process from running the index creation statement, then check that log. I have used just one url for the data that can be easily accessed publicly, for the sake of the demonstration. You need to have a valid index before you can rebuild and/or optimize. I gather that you are not seeing certain values in your tokens that you know are in the data accessed by the URL and that rebuilding and optimizing are taking a very long time, so you have copied just one id and url for testing, which is an excellent idea. You might even start with just something simple like I have below. There are so many possibilities. You need to establish whether you can index just one url and get the tokens you want. If not, then there is something wrong with the whole indexing process, perhaps something as simple as stop words or minimal length for tokens to be indexed. If you do get the expected tokens indexing just one url, then it is more likely the size of the data from a number of url's to be indexed. We need to establish which it is before continuing.
C##SCOTT@XE_21.3.0.0.0> -- create table, url_datastore preference, and index:
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE test_token
2 (assigned_id VARCHAR2(11),
3 text_content_url VARCHAR2(2000))
4 /
Table created.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('URL_PREF','URL_DATASTORE');
3 ctx_ddl.set_attribute('URL_PREF','NO_PROXY','http://www.example.com/index.html');
4 ctx_ddl.set_attribute('URL_PREF','Timeout','10');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> CREATE INDEX test_token_url1 ON test_token (text_content_url)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('DATASTORE url_pref')
4 /
Index created.
C##SCOTT@XE_21.3.0.0.0> -- insert data and commit:
C##SCOTT@XE_21.3.0.0.0> INSERT into test_token (assigned_id, text_content_url)
2 VALUES ('MCP-135', ' http://www.example.com/index.html')
3 /
1 row created.
C##SCOTT@XE_21.3.0.0.0> COMMIT
2 /
Commit complete.
C##SCOTT@XE_21.3.0.0.0> -- nothing is tokenized yet:
C##SCOTT@XE_21.3.0.0.0> select distinct token_text
2 , token_type
3 , token_count
4 FROM dr$test_token_url1$I
5 order by token_text, token_type
6 /
no rows selected
C##SCOTT@XE_21.3.0.0.0> -- create log of rebuild and optimize of index:
C##SCOTT@XE_21.3.0.0.0> EXEC CTX_OUTPUT.START_LOG ('TESTLOG.TXT')
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> ALTER INDEX TEST_TOKEN_URL1 REBUILD ONLINE;
Index altered.
C##SCOTT@XE_21.3.0.0.0> EXEC CTX_DDL.OPTIMIZE_INDEX('test_token_url1', 'FULL');
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> EXEC CTX_OUTPUT.END_LOG
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> -- check for errors:
C##SCOTT@XE_21.3.0.0.0> SELECT * FROM CTX_USER_INDEX_ERRORS
2 /
no rows selected
C##SCOTT@XE_21.3.0.0.0> -- check log:
C##SCOTT@XE_21.3.0.0.0> SELECT PAYLOAD
2 FROM V$DIAG_TRACE_FILE_CONTENTS
3 WHERE COMPONENT_NAME='CONTEXT_INDEX'
4 AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
5 ORDER BY TIMESTAMP
6 /
PAYLOAD
--------------------------------------------------------------------------------
13-09-38 08/14/23 Oracle Text, 21.0.0.0.0
13-09-38 08/14/23 begin logging
13-09-38 08/14/23
13-09-39 08/14/23 resume index: C##SCOTT.TEST_TOKEN_URL1
13-09-39 08/14/23 Using memory parameter 67108864
13-09-39 08/14/23 Begin document indexing
13-09-39 08/14/23 Begin writing index to database.
13-09-39 08/14/23 Errors reading documents: 0
13-09-39 08/14/23 Index data for 1 documents to be written to database
13-09-39 08/14/23 memory use: 411794
13-09-39 08/14/23 Start writing index to $I.
13-09-39 08/14/23 Begin sorting the inverted list.
13-09-39 08/14/23 End sorting the inverted list.
13-09-39 08/14/23 Writing index data ($I) to database.
13-09-39 08/14/23 Using direct path writes for "C##SCOTT"."DR$TEST_TOKEN_URL1$I"
13-09-39 08/14/23 Inserting using direct path loading
13-09-39 08/14/23 Wrote 15 rows (0 big rows) to $I.
13-09-39 08/14/23 Finish writing index to $I.
13-09-39 08/14/23 Writing index data ($K) to database.
13-09-39 08/14/23 Wrote 1 rows to $K.
13-09-39 08/14/23 index data written to database.
13-09-39 08/14/23 possibly start in-memory indexing again
13-09-39 08/14/23 Using memory parameter 67108864
13-09-39 08/14/23 End of document indexing. 1 documents indexed.
13-09-39 08/14/23 Creating Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$X"
13-09-39 08/14/23 Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$X" created
13-09-39 08/14/23 Creating Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$KD"
13-09-39 08/14/23 Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$KD" created
13-09-39 08/14/23 Creating Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$KR"
13-09-39 08/14/23 Oracle index "C##SCOTT"."DR$TEST_TOKEN_URL1$KR" created
13-09-39 08/14/23 Locking table TEST_TOKEN
13-09-39 08/14/23 Processing pending DMLs
13-09-39 08/14/23 Trying to get optimize xlock.
13-09-39 08/14/23 Got optimize xlock.
13-09-39 08/14/23 sync DocID mappings: C##SCOTT.TEST_TOKEN_URL1
13-09-39 08/14/23 drekGetNextID:
13-09-39 08/14/23 drekGetNextID: nrow=0, next=0
13-09-39 08/14/23 drekInitB:
13-09-39 08/14/23 drekInitB: nrow=0, next=90441752
13-09-39 08/14/23 drekGetMaxDocID: idx=1674, ixp=0
13-09-39 08/14/23 drekGetMaxDocID: max=2
13-09-39 08/14/23 drekInitB: next=2
13-09-39 08/14/23 drekGetNextID: next=2
13-09-39 08/14/23 drekSetNextID: next=2
13-09-39 08/14/23 process $N for optimize: C##SCOTT.TEST_TOKEN_URL1
13-09-39 08/14/23 Finish processing $N for optimize.
13-09-39 08/14/23 optimize full: C##SCOTT.TEST_TOKEN_URL1
13-09-39 08/14/23 starting new optimization from beginning of $I table
13-09-39 08/14/23 0 invalid docids read from $N table
13-09-39 08/14/23 full optimize initialization complete
13-09-39 08/14/23 starting $I row optimization
13-09-39 08/14/23 deleting rows from $I table
13-09-39 08/14/23 deleted 0 rows from $I table
13-09-39 08/14/23 $I table optimize full C##SCOTT.TEST_TOKEN_URL1 complete
13-09-39 08/14/23 process $N for optimize: C##SCOTT.TEST_TOKEN_URL1
13-09-39 08/14/23 Finish processing $N for optimize.
13-09-39 08/14/23 Trying to release optimize lock.
13-09-39 08/14/23 Released optimize lock.
13-09-39 08/14/23 C##SCOTT.TEST_TOKEN_URL1 FULL Optimize Index Complete
13-09-39 08/14/23 log
13-09-39 08/14/23 logging halted
61 rows selected.
C##SCOTT@XE_21.3.0.0.0> -- check tokens:
C##SCOTT@XE_21.3.0.0.0> select distinct token_text
2 , token_type
3 , token_count
4 FROM dr$test_token_url1$I
5 order by token_text, token_type
6 /
TOKEN_TEXT TOKEN_TYPE TOKEN_COUNT
------------------------------ ---------- -----------
ASKING 0 1
COORDINATION 0 1
DOCUMENTS 0 1
DOMAIN 0 1
EXAMPLE 0 1
EXAMPLES 0 1
ILLUSTRATIVE 0 1
INFORMATION 0 1
LITERATURE 0 1
MAY 0 1
MORE 0 1
PERMISSION 0 1
PRIOR 0 1
USE 0 1
WITHOUT 0 1
15 rows selected.
|
|
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688030 is a reply to message #688029] |
Mon, 14 August 2023 15:50 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I am running this from TOAD. No, it is not in production.
Here is the output that I am getting:
Commit complete.
Index dropped.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
>> CREATE INDEX kjc_test_index_url1
ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_URL_DATASTORE
LEXER KJC_TEST_INDEX_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_storage
FILTER KJC_TEST_INDEX_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
PARALLEL 5
Error at line 52
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drixmd.PurgeKGL
ORA-20000: Oracle Text error:
ORA-04021: timeout occurred while waiting to lock object MI.KJC_TEST_INDEX_URL1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRIXMD", line 2664
ORA-00001: unique constraint (CTXSYS.DRC$IDX_COLSPEC) violated
ORA-06512: at "CTXSYS.DRIXMD", line 2639
DRG-50610: internal error: kglpurge []
DRG-10507: duplicate index name: KJC_TEST_INDEX_URL1
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
It took 31 minutes. Any suggestions on what to try?
[Updated on: Mon, 14 August 2023 15:52] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688031 is a reply to message #688030] |
Mon, 14 August 2023 16:35 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I removed the Drop index. So now instead of taking 20 minutes to drop the index it is taking 20 minutes to drop the table. In all it still takes 31 minutes to run the script and I am still getting the same error message.
Here is the script:
commit;
DROP TABLE KJC_TEST_INDEX;
CREATE TABLE KJC_TEST_INDEX
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('kjc_test_index_storage');
END;
/
BEGIN
ctx_ddl.create_preference('kjc_test_index_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('kjc_test_index_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout','10');
ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_URL_DATASTORE','URL_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','Timeout','10');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_URL_DATASTORE','HTTP_PROXY', 'webcache-2.inel.gov:8080');
END;
/
INSERT into kjc_test_index (assigned_id, text_content_url)
VALUES('MCP-135', 'file:///usr/edms/web/edm23m/10881/10880871.docx');
commit;
CREATE INDEX kjc_test_index_url1
ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_URL_DATASTORE
LEXER KJC_TEST_INDEX_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_storage
FILTER KJC_TEST_INDEX_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
PARALLEL 5;
Commit complete.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
>> CREATE INDEX kjc_test_index_url1
ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_URL_DATASTORE
LEXER KJC_TEST_INDEX_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_storage
FILTER KJC_TEST_INDEX_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
PARALLEL 5
Error at line 51
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drixmd.PurgeKGL
ORA-20000: Oracle Text error:
ORA-04021: timeout occurred while waiting to lock object MI.KJC_TEST_INDEX_URL1
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRIXMD", line 2664
ORA-00001: unique constraint (CTXSYS.DRC$IDX_COLSPEC) violated
ORA-06512: at "CTXSYS.DRIXMD", line 2639
DRG-50610: internal error: kglpurge []
DRG-10507: duplicate index name: KJC_TEST_INDEX_URL1
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 320
Script stopped on line 51.
|
|
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688034 is a reply to message #688033] |
Mon, 14 August 2023 17:22 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
The index only exists once.
It appears that the index is being created even though the error message leads you to believe otherwise.
Select * from all_indexes where index_name = 'KJC_TEST_INDEX_URL1'
Shows that the above index does exist and it is on the table: KJC_TEST_INDEX.
Though the index was created and only exists once it doesn't appear to be working. There are no tokens. The following query does not return anything, well it is still running. It has been running for 10 minutes now.
select distinct token_text
, token_type
, token_count
FROM dr$test_token_url1$I
order by token_text, token_type
[Updated on: Mon, 14 August 2023 17:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688038 is a reply to message #688037] |
Mon, 14 August 2023 18:28 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I changed the names, and removed the "parallel 5" and it seemed to have worked. Here is the script that I ran:
commit;
--DROP TABLE KJC_TEST_INDEX_1;
CREATE TABLE KJC_TEST_INDEX_1
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
--BEGIN
-- CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_1_storage');
--END;
--/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_1_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
--BEGIN
-- CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_1_Lex_URL');
--END;
--/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_1_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_Lex_URL', 'whitespace', '_-');
END;
/
--BEGIN
-- CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_1_AUTO_FILTER');
--END;
--/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_1_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_AUTO_FILTER', 'Timeout','10');
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
--BEGIN
-- CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_1_URL_DATASTORE');
--END;
--/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_1_URL_DATASTORE','URL_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_1_URL_DATASTORE','Timeout','10');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_1_URL_DATASTORE','HTTP_PROXY', 'webcache-2.inel.gov:8080');
END;
/
INSERT into KJC_TEST_INDEX_1 (assigned_id, text_content_url)
VALUES('MCP-135', 'file:///usr/edms/web/edm23m/10881/10880871.docx');
commit;
CREATE INDEX KJC_TEST_INDEX_1_url1
ON KJC_TEST_INDEX_1(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_1_URL_DATASTORE
LEXER KJC_TEST_INDEX_1_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_1_storage
FILTER KJC_TEST_INDEX_1_AUTO_FILTER
MEMORY 50M
NOPOPULATE') ;
Commit complete.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
Index created.
However, it doesn't seem to have indexed anything. Zero records were returned from the following select statement:
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_1_url1$I
order by token_text, token_type
[Updated on: Mon, 14 August 2023 18:29] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688041 is a reply to message #688040] |
Mon, 14 August 2023 18:58 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Took most of the day but I am getting there. I have successfully indexed the document. I am now exactly where I am at in production.
The problem is that it is not indexing the whole document. I have the following text in this document that does not show up in the tokens:
I would have expected to see the tokens:
MCP, 9395, LST, 1001, WIP, 5, ...
When I executed the following query:
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_1_url1$I
order by token_text, token_type;
SELECT * FROM CTX_USER_INDEX_ERRORS;
returns no records.
Thank you for your help!!!
[Updated on: Mon, 14 August 2023 19:21] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688042 is a reply to message #688041] |
Mon, 14 August 2023 19:48 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When I ran the script where you changed the names and removed parallel, I got the same results as you. Hopefully, you left everything intact at that point. Please run the script below. I have posted the output that I got below that. If you get the same, including tokens, then that will narrow down the remaining problem to a proxy issue of some sort.
-- script for you to run:
DELETE FROM KJC_TEST_INDEX_1 WHERE assigned_id = 'MCP-135'
/
INSERT into KJC_TEST_INDEX_1 (assigned_id, text_content_url)
VALUES ('ABC-123', 'http://www.example.com/index.html')
/
COMMIT
/
BEGIN
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_URL_DATASTORE',
'NO_PROXY','http://www.example.com/index.html');
END;
/
ALTER INDEX KJC_TEST_INDEX_1_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_1_URL_DATASTORE')
/
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_1_url1$I
order by token_text, token_type
/
-- my output from the above script:
C##SCOTT@XE_21.3.0.0.0> DELETE FROM KJC_TEST_INDEX_1 WHERE assigned_id = 'MCP-135'
2 /
1 row deleted.
C##SCOTT@XE_21.3.0.0.0> INSERT into KJC_TEST_INDEX_1 (assigned_id, text_content_url)
2 VALUES ('ABC-123', 'http://www.example.com/index.html')
3 /
1 row created.
C##SCOTT@XE_21.3.0.0.0> COMMIT
2 /
Commit complete.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.set_attribute('KJC_TEST_INDEX_1_URL_DATASTORE',
3 'NO_PROXY','http://www.example.com/index.html');
4 END;
5 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> ALTER INDEX KJC_TEST_INDEX_1_url1 REBUILD ONLINE
2 PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_1_URL_DATASTORE')
3 /
Index altered.
C##SCOTT@XE_21.3.0.0.0> select distinct token_text
2 , token_type
3 , token_count
4 FROM dr$kjc_test_index_1_url1$I
5 order by token_text, token_type
6 /
TOKEN_TEXT TOKEN_TYPE TOKEN_COUNT
------------------------------ ---------- -----------
ASKING 0 1
COORDINATION 0 1
DOCUMENTS 0 1
DOMAIN 0 1
EXAMPLE 0 1
EXAMPLES 0 1
FOR 0 1
ILLUSTRATIVE 0 1
IN 0 1
INFORMATION 0 1
IS 0 1
LITERATURE 0 1
MAY 0 1
MORE 0 1
OR 0 1
PERMISSION 0 1
PRIOR 0 1
THIS 0 1
USE 0 1
WITHOUT 0 1
YOU 0 1
21 rows selected.
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688043 is a reply to message #688042] |
Mon, 14 August 2023 20:50 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
It did not index the document.
Here is the script that I ran:
DELETE FROM KJC_TEST_INDEX_1 WHERE assigned_id = 'MCP-135'
/
INSERT into KJC_TEST_INDEX_1 (assigned_id, text_content_url)
VALUES ('ABC-123', 'http://www.example.com/index.html')
/
COMMIT
/
BEGIN
ctx_ddl.set_attribute('KJC_TEST_INDEX_1_URL_DATASTORE',
'NO_PROXY','http://www.example.com/index.html');
END;
/
EXEC ctxsys.CTX_OUTPUT.START_LOG ('TESTLOG.TXT');
ALTER INDEX KJC_TEST_INDEX_1_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_1_URL_DATASTORE')
/
EXEC ctxsys.CTX_OUTPUT.END_LOG;
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_1_url1$I
order by token_text, token_type
/
-- check log:
SELECT PAYLOAD
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE COMPONENT_NAME='CONTEXT_INDEX'
AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
ORDER BY TIMESTAMP;
0 rows deleted.
1 row created.
Commit complete.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Index altered.
PL/SQL procedure successfully completed.
no rows selected.
PAYLOAD
--------------------------------------------------------------------------------
19:47:05 08/14/23 Oracle Text, 19.0.0.0.0
19:47:05 08/14/23 begin logging
19:47:05 08/14/23
19:47:05 08/14/23 check text datastore access
19:47:05 08/14/23 check pdb lockdown
19:47:05 08/14/23 Locking table KJC_TEST_INDEX_1
19:47:05 08/14/23 Locking table KJC_TEST_INDEX_1
19:47:05 08/14/23 lock acquired
19:47:06 08/14/23 resume index: MI.KJC_TEST_INDEX_1_URL1
19:47:06 08/14/23 Using memory parameter 67108864
19:47:06 08/14/23 Begin document indexing
19:47:26 08/14/23 End of document indexing. 0 documents indexed.
19:47:26 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X"
19:47:26 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X" created
19:47:26 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD"
19:47:26 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD" created
19:47:26 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR"
19:47:26 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR" created
19:47:26 08/14/23 Locking table KJC_TEST_INDEX_1
19:47:26 08/14/23 Processing pending DMLs
19:47:26 08/14/23 log
19:47:26 08/14/23 logging halted
One other thing, when I tried to index my document MCP-135, it was kind of successfull. It indexed 200ish tokens, but not all of them. That is my problem, it is not indexing all of the words.
Found something else.
SELECT * FROM CTX_USER_INDEX_ERRORS
returned the following:
KJC_TEST_INDEX_1_URL1 8/14/2023 7:43:05 PM AABnI9AAAAAFIgTAAB DRG-11614: URL store: communication with host specified in http://www.example.com/index.html timed out
KJC_TEST_INDEX_1_URL1 8/14/2023 7:47:16 PM AABnI9AAAAAFIgTAAB DRG-11614: URL store: communication with host specified in http://www.example.com/index.html timed out
KJC_TEST_INDEX_1_URL1 8/14/2023 7:47:26 PM AABnI9AAAAAFIgTAAC DRG-11614: URL store: communication with host specified in http://www.example.com/index.html timed out
[Updated on: Mon, 14 August 2023 20:55] Report message to a moderator |
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688046 is a reply to message #688045] |
Tue, 15 August 2023 03:17 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is my test with network_datastore and timeout of 3600 seconds and no_proxy and 'http://www.example.com/index.html'. The error on the dropping of the url_datastore is only because it had been previously dropped, so that can be ignored.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> DROP TABLE KJC_TEST_INDEX;
Table dropped.
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE KJC_TEST_INDEX
2 (assigned_id VARCHAR2(11),
3 text_content_url VARCHAR2(2000));
Table created.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('kjc_test_index_storage');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('kjc_test_index_storage', 'BASIC_STORAGE');
3 ctx_ddl.set_attribute('kjc_test_index_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
4 ctx_ddl.set_attribute('kjc_test_index_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
5 ctx_ddl.set_attribute('kjc_test_index_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
6 ctx_ddl.set_attribute('kjc_test_index_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
7 ctx_ddl.set_attribute('kjc_test_index_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
8 END;
9 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_Lex_URL');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_Lex_URL', 'BASIC_LEXER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_Lex_URL', 'whitespace', '_-');
4 END;
5 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_AUTO_FILTER');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_AUTO_FILTER','AUTO_FILTER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout','120');
4 ctx_ddl.set_attribute('KJC_TEST_INDEX_AUTO_FILTER', 'Timeout_type','HEURISTIC');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('URL_DATASTORE');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: URL_DATASTORE
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.DRVLSB", line 50
ORA-06512: at "CTXSYS.CTX_DDL", line 57
ORA-06512: at line 2
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('NETWORK_PREF');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('NETWORK_PREF','NETWORK_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE('NETWORK_PREF','Timeout','3600');
4 ctx_ddl.set_attribute('NETWORK_PREF','NO_PROXY','http://www.example.com/index.html');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> INSERT into kjc_test_index (assigned_id, text_content_url)
2 VALUES ('ABC-123', 'http://www.example.com/index.html');
1 row created.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> COLUMN token_textFORMATA130
SP2-0046: COLUMN 'token_textFORMATA130' not defined
C##SCOTT@XE_21.3.0.0.0> CREATE INDEX kjc_test_index_url1
2 ON KJC_TEST_INDEX(TEXT_CONTENT_URL)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS
5 ('DATASTORE NETWORK_PREF
6 LEXER KJC_TEST_INDEX_Lex_URL
7 STOPLIST CTXSYS.EMPTY_STOPLIST
8 STORAGE KJC_TEST_INDEX_storage
9 FILTER KJC_TEST_INDEX_AUTO_FILTER
10 MEMORY 50M
11 NOPOPULATE')
12 /
Index created.
C##SCOTT@XE_21.3.0.0.0> COLUMN token_text FORMAT A30
C##SCOTT@XE_21.3.0.0.0> select distinct token_text
2 , token_type
3 , token_count
4 FROM dr$kjc_test_index_1_url1$I
5 order by token_text, token_type
6 /
TOKEN_TEXT TOKEN_TYPE TOKEN_COUNT
------------------------------ ---------- -----------
ASKING 0 1
COORDINATION 0 1
DOCUMENTS 0 1
DOMAIN 0 1
EXAMPLE 0 1
EXAMPLES 0 1
FOR 0 1
ILLUSTRATIVE 0 1
IN 0 1
INFORMATION 0 1
IS 0 1
LITERATURE 0 1
MAY 0 1
MORE 0 1
OR 0 1
PERMISSION 0 1
PRIOR 0 1
THIS 0 1
USE 0 1
WITHOUT 0 1
YOU 0 1
21 rows selected.
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688048 is a reply to message #688046] |
Tue, 15 August 2023 08:47 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Here is the script that I ran. I had to change a few things in order to get it to run.
commit;
DROP TABLE KJC_TEST_INDEX_2;
CREATE TABLE KJC_TEST_INDEX_2
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_storage');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout','120');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','NETWORK_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','Timeout','3600');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_NETWORK_PREF','NO_PROXY','http://www.example.com/index.html');
END;
/
INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
VALUES ('ABC-123', 'http://www.example.com/index.html');
commit;
CREATE INDEX KJC_TEST_INDEX_2_url1
ON KJC_TEST_INDEX_2(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF
LEXER KJC_TEST_INDEX_2_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_2_storage
FILTER KJC_TEST_INDEX_2_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
/
EXEC ctxsys.CTX_OUTPUT.START_LOG ('TESTLOG.TXT');
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
/
EXEC ctxsys.CTX_OUTPUT.END_LOG;
COLUMN token_text FORMAT A30
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_2_url1$I
order by token_text, token_type
/
-- check log:
COLUMN payload FORMAT A75
SELECT PAYLOAD
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE COMPONENT_NAME='CONTEXT_INDEX'
AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
ORDER BY TIMESTAMP;
commit;
SELECT * FROM CTX_USER_INDEX_ERRORS
Commit complete.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
>> BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
Error at line 45
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: KJC_TEST_INDEX_2_URL_DATASTORE
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.DRVLSB", line 50
ORA-06512: at "CTXSYS.CTX_DDL", line 45
ORA-06512: at line 2
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
Index created.
PL/SQL procedure successfully completed.
Index altered.
PL/SQL procedure successfully completed.
TOKEN_TEXT TOKEN_TYPE TOKEN_COUNT
------------------------------ ---------- -----------
ASKING 0 1
COORDINATION 0 1
DOCUMENTS 0 1
DOMAIN 0 1
EXAMPLE 0 1
EXAMPLES 0 1
FOR 0 1
ILLUSTRATIVE 0 1
IN 0 1
INFORMATION 0 1
IS 0 1
LITERATURE 0 1
MAY 0 1
MORE 0 1
OR 0 1
PERMISSION 0 1
PRIOR 0 1
THIS 0 1
USE 0 1
WITHOUT 0 1
YOU 0 1
21 rows selected.
PAYLOAD
---------------------------------------------------------------------------
07:39:04 08/15/23 Oracle Text, 19.0.0.0.0
07:39:04 08/15/23 begin logging
07:39:04 08/15/23
07:39:04 08/15/23 Locking table KJC_TEST_INDEX_2
07:39:04 08/15/23 Locking table KJC_TEST_INDEX_2
07:39:04 08/15/23 lock acquired
07:39:04 08/15/23 resume index: MI.KJC_TEST_INDEX_2_URL1
PAYLOAD
---------------------------------------------------------------------------
07:39:04 08/15/23 Using memory parameter 67108864
07:39:04 08/15/23 Begin document indexing
07:39:04 08/15/23 Begin writing index to database.
07:39:04 08/15/23 Errors reading documents: 0
07:39:04 08/15/23 Index data for 1 documents to be written to database
07:39:04 08/15/23 memory use: 412418
07:39:04 08/15/23 Start writing index to $I.
PAYLOAD
---------------------------------------------------------------------------
07:39:04 08/15/23 Begin sorting the inverted list.
07:39:04 08/15/23 End sorting the inverted list.
07:39:04 08/15/23 Writing index data ($I) to database.
07:39:05 08/15/23 Inserting using direct path loading
07:39:05 08/15/23 Wrote 21 rows (0 big rows) to $I.
07:39:05 08/15/23 Finish writing index to $I.
07:39:05 08/15/23 Writing index data ($K) to database.
PAYLOAD
---------------------------------------------------------------------------
07:39:05 08/15/23 Wrote 1 rows to $K.
07:39:05 08/15/23 index data written to database.
07:39:05 08/15/23 possibly start in-memory indexing again
07:39:05 08/15/23 Using memory parameter 67108864
07:39:05 08/15/23 End of document indexing. 1 documents indexed.
07:39:05 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X"
07:39:05 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X" created
PAYLOAD
---------------------------------------------------------------------------
07:39:05 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD"
07:39:05 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD" created
07:39:05 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR"
07:39:05 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR" created
07:39:05 08/15/23 Locking table KJC_TEST_INDEX_2
07:39:05 08/15/23 Processing pending DMLs
07:39:05 08/15/23 log
PAYLOAD
---------------------------------------------------------------------------
07:39:05 08/15/23 logging halted
36 rows selected.
Commit complete.
Script stopped on line 107.
OK, so it is indexing correctly now, right?
So now how do I get it to index: 'file:///usr/edms/web/edm23m/10881/10880871.docx'
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688050 is a reply to message #688049] |
Tue, 15 August 2023 10:57 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I think I am getting closer. Here is what I have so far, but it is failing. I am getting a certificate error. How do I resolve that? See error message at bottom.
commit;
DROP TABLE KJC_TEST_INDEX_2;
CREATE TABLE KJC_TEST_INDEX_2
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_storage');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout','120');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','NETWORK_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','Timeout','3600');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_NETWORK_PREF','NO_PROXY','http://www.example.com/index.html');
END;
/
INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
VALUES ('ABC-123', 'http://acedmvaultprd.inl.gov/edm22h/10160/10160236.docx');
commit;
CREATE INDEX KJC_TEST_INDEX_2_url1
ON KJC_TEST_INDEX_2(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF
LEXER KJC_TEST_INDEX_2_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_2_storage
FILTER KJC_TEST_INDEX_2_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
/
EXEC ctxsys.CTX_OUTPUT.START_LOG ('TESTLOG.TXT');
--ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
-- PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
--/
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE;
EXEC ctxsys.CTX_OUTPUT.END_LOG;
COLUMN token_text FORMAT A30
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_2_url1$I
order by token_text, token_type
/
-- check log:
COLUMN payload FORMAT A75
SELECT PAYLOAD
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE COMPONENT_NAME='CONTEXT_INDEX'
AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
ORDER BY TIMESTAMP;
commit;
SELECT * FROM CTX_USER_INDEX_ERRORS order by 2 desc
Commit complete.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
>> BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
Error at line 45
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: KJC_TEST_INDEX_2_URL_DATASTORE
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.DRVLSB", line 50
ORA-06512: at "CTXSYS.CTX_DDL", line 45
ORA-06512: at line 2
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
Index created.
PL/SQL procedure successfully completed.
Index altered.
PL/SQL procedure successfully completed.
no rows selected.
PAYLOAD
---------------------------------------------------------------------------
09:49:51 08/15/23 Oracle Text, 19.0.0.0.0
09:49:51 08/15/23 begin logging
09:49:51 08/15/23
09:49:51 08/15/23 resume index: MI.KJC_TEST_INDEX_2_URL1
09:49:51 08/15/23 Using memory parameter 67108864
09:49:51 08/15/23 Begin document indexing
09:49:51 08/15/23 End of document indexing. 0 documents indexed.
PAYLOAD
---------------------------------------------------------------------------
09:49:51 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X"
09:49:51 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X" created
09:49:51 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD"
09:49:51 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD" created
09:49:51 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR"
09:49:51 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR" created
09:49:51 08/15/23 Locking table KJC_TEST_INDEX_2
PAYLOAD
---------------------------------------------------------------------------
09:49:51 08/15/23 Processing pending DMLs
09:49:51 08/15/23 log
09:49:51 08/15/23 logging halted
17 rows selected.
Commit complete.
KJC_TEST_INDEX_2_URL1 8/15/2023 9:49:51 AM AABnRBAAAAAFxvjAAA DRG-11622: URL store: unknown HTTP error 3823243664 getting http://acedmvaultprd.inl.gov/edm22h/10160/10160236.docx
DRG-50857: oracle error in read_into_lobLocator
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDML", line 2183
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512:
[Updated on: Tue, 15 August 2023 11:26] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688051 is a reply to message #688050] |
Tue, 15 August 2023 11:52 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
I have modified the script to include this:
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','NETWORK_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','Timeout','3600');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_NETWORK_PREF','NO_PROXY','http://www.example.com/index.html, http://acedmvaultprd.inl.gov, acedmvaultprd, acedmvaultprd.inl.gov');
END;
/
and changed the URL to the document to be this:http://acedmvaultprd.inl.gov/edm22h/10160/10160236.docx
But I am still getting a certificate error.
DRG-11622: URL store: unknown HTTP error 3823243664 getting http://acedmvaultprd.inl.gov/edm22h/10160/10160236.docx
DRG-50857: oracle error in read_into_lobLocator
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDML", line 2183
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512:
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688052 is a reply to message #688051] |
Tue, 15 August 2023 12:58 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you change this:
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','NETWORK_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','Timeout','3600');
ctx_ddl.set_attribute
('KJC_TEST_INDEX_2_NETWORK_PREF',
'NO_PROXY',
'http://www.example.com/index.html,
http://acedmvaultprd.inl.gov,
acedmvaultprd,
acedmvaultprd.inl.gov');
END;
/
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
/
[Updated on: Tue, 15 August 2023 12:58] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688053 is a reply to message #688052] |
Tue, 15 August 2023 13:16 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Thank you!!
Just for my understanding. Why did I need to do that?
Here is my thinking.
I dropped the table, and thus the index was dropped.
I also dropped the preference: KJC_TEST_INDEX_2_NETWORK_PREF
and then recreated.
I then recreated the index using KJC_TEST_INDEX_2_NETWORK_PREF.
So what is the reason why I need to do this:
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
OK, I did that and still getting the same error:
DRG-11622: URL store: unknown HTTP error 3823243664 getting http://acedmvaultprd.inl.gov/edm23m/10881/10880871.docx
DRG-50857: oracle error in read_into_lobLocator
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRVDML", line 2183
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1288
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 651
ORA-06512:
Here is the script that I ran:
commit;
DROP TABLE KJC_TEST_INDEX_2;
CREATE TABLE KJC_TEST_INDEX_2
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_storage');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout','120');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','NETWORK_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','Timeout','3600');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_NETWORK_PREF','NO_PROXY','http://www.example.com/index.html, http://acedmvaultprd.inl.gov, acedmvaultprd, acedmvaultprd.inl.gov');
END;
/
-- INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
-- VALUES ('ABC-123', 'http://acedmvaultprd.inl.gov/edm22h/10160/10160236.docx');
INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
VALUES ('ABC-124', 'http://acedmvaultprd.inl.gov/edm23m/10881/10880871.docx');
commit;
CREATE INDEX KJC_TEST_INDEX_2_url1
ON KJC_TEST_INDEX_2(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF
LEXER KJC_TEST_INDEX_2_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_2_storage
FILTER KJC_TEST_INDEX_2_AUTO_FILTER
MEMORY 50M
NOPOPULATE')
/
EXEC ctxsys.CTX_OUTPUT.START_LOG ('TESTLOG.TXT');
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
/
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE;
EXEC ctxsys.CTX_OUTPUT.END_LOG;
COLUMN token_text FORMAT A30
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_2_url1$I
order by token_text, token_type
/
-- check log:
COLUMN payload FORMAT A75
SELECT PAYLOAD
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE COMPONENT_NAME='CONTEXT_INDEX'
AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
ORDER BY TIMESTAMP;
commit;
SELECT * FROM CTX_USER_INDEX_ERRORS order by 2 desc;
Commit complete.
Table dropped.
Table created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
>> BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_URL_DATASTORE');
END;
Error at line 45
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: KJC_TEST_INDEX_2_URL_DATASTORE
ORA-06512: at "CTXSYS.DRUE", line 186
ORA-06512: at "CTXSYS.DRVLSB", line 50
ORA-06512: at "CTXSYS.CTX_DDL", line 45
ORA-06512: at line 2
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
Index created.
PL/SQL procedure successfully completed.
Index altered.
Index altered.
PL/SQL procedure successfully completed.
no rows selected.
PAYLOAD
---------------------------------------------------------------------------
12:07:47 08/15/23 Oracle Text, 19.0.0.0.0
12:07:47 08/15/23 begin logging
12:07:47 08/15/23
12:07:47 08/15/23 Locking table KJC_TEST_INDEX_2
12:07:47 08/15/23 Locking table KJC_TEST_INDEX_2
12:07:47 08/15/23 lock acquired
12:07:47 08/15/23 resume index: MI.KJC_TEST_INDEX_2_URL1
PAYLOAD
---------------------------------------------------------------------------
12:07:47 08/15/23 Using memory parameter 67108864
12:07:47 08/15/23 Begin document indexing
12:07:47 08/15/23 End of document indexing. 0 documents indexed.
12:07:47 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X"
12:07:47 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X" created
12:07:47 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD"
12:07:47 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD" created
PAYLOAD
---------------------------------------------------------------------------
12:07:47 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR"
12:07:47 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR" created
12:07:47 08/15/23 Locking table KJC_TEST_INDEX_2
12:07:47 08/15/23 Processing pending DMLs
12:07:47 08/15/23
12:07:48 08/15/23 resume index: MI.KJC_TEST_INDEX_2_URL1
12:07:48 08/15/23 Using memory parameter 67108864
PAYLOAD
---------------------------------------------------------------------------
12:07:48 08/15/23 Begin document indexing
12:07:48 08/15/23 End of document indexing. 0 documents indexed.
12:07:48 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X"
12:07:48 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$X" created
12:07:48 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD"
12:07:48 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KD" created
12:07:48 08/15/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR"
PAYLOAD
---------------------------------------------------------------------------
12:07:48 08/15/23 Oracle index "MI"."DR$KJC_TEST_INDEX_2_URL1$KR" created
12:07:48 08/15/23 Locking table KJC_TEST_INDEX_2
12:07:48 08/15/23 Processing pending DMLs
12:07:48 08/15/23 log
12:07:48 08/15/23 logging halted
33 rows selected.
Commit complete.
[Updated on: Tue, 15 August 2023 13:45] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688055 is a reply to message #688053] |
Tue, 15 August 2023 13:55 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quote:
Just for my understanding. Why did I need to do that?
Here is my thinking.
I dropped the table, and thus the index was dropped.
I also dropped the preference: KJC_TEST_INDEX_2_NETWORK_PREF
and then recreated.
I then recreated the index using KJC_TEST_INDEX_2_NETWORK_PREF.
So what is the reason why I need to do this:
ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
PARAMETERS ('REPLACE DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF')
You did not need to it. I did not realize that you had dropped and recreated the whole index, including the new network_datastore preference. I thought you just modified the preference, then did an index rebuild without including the replacement of the network_datastore preference.
[Updated on: Tue, 15 August 2023 14:03] Report message to a moderator |
|
|
|
| Re: How to determine last tiime Content was indexed [message #688056 is a reply to message #688049] |
Tue, 15 August 2023 14:01 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
kjcook wrote on Tue, 15 August 2023 07:07
It appears that I am supposed to use a DIRECTORY_DATASTORE which uses a directory object. We have 1000s of folders and we create a new folder every 2 weeks. Any suggestion on how to do this?
Why do you think you need a directory_datastore? Are the files that you are trying to access on your local file system or on the world wide web / internet?
[Updated on: Tue, 15 August 2023 14:03] Report message to a moderator |
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688059 is a reply to message #688057] |
Tue, 15 August 2023 15:24 |
|
kjcook
Messages: 31
Registered: January 2016
|
Member |
|
|
Thank you for your help.
The URL is valid and I can get to the documents from my browser. I am in the process of verifying that I can get to them from the database server. I am trying to flush out the certificate error. The documents are only available from with in our intranet. The files/documents are local.
Using a directory_datastore, as I understand it, would be a major project. I really don't think that is an option for us.
[Updated on: Tue, 15 August 2023 15:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How to determine last tiime Content was indexed [message #688064 is a reply to message #688063] |
Tue, 15 August 2023 16:36 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I got it working. The auto_filter and the nopopulate are apparently incompatible with the directory_datastore. If those are important and you need some sort of substitute or workaround let me know and I will see what i can figure out.
In the meanwhile I will supply you with my script for you to run and test for just one directory initially. I will also post my the results of my run.
I tried to change as few things as possible, including prior names. Once you have tested it, then you may want to change the names to be more descriptive of what they are. It kind of confused me to begin with that you have a table with the word index in its name.
I marked all the changes as comments along the left margin in capitals.
There are some things that are specific to my system that you will need to change before running it on yours, such as operating system directory name and the file name of some text file for testing in that directory. I created an example.dat file that contains the same data as http://www.example.com/index.html that I was previously accessing and put it in my c:\my_oracle_files operating system directory.
There are 2 scripts that need to be run. The first as sysdba to create the Oracle directory object and grant read privileges. I granted the privileges to user C##SCOTT on my system. You will need to have the privilege granted to the creator fo the index, presumably you. The second script you can run from your own schema that you have been running everything else from. I have broken the second one into pieces to make it easier to see where the changes are.
-- RUN AS SYSDBA:
-- CREATE ORACLE DIRECTORY OBJECT MYHOME FOR OPERATING SYSTEM DIRECTORY c:\my_oracle_files:
CREATE OR REPLACE DIRECTORY MYHOME as 'c:\my_oracle_files\'
/
-- GRANT READ PRIVILEGES TO THE ORACLE DIRECTORY OBJECT:
GRANT READ ON DIRECTORY MYHOME TO C##SCOTT
/
DROP TABLE KJC_TEST_INDEX_2;
CREATE TABLE KJC_TEST_INDEX_2
(assigned_id VARCHAR2(11),
text_content_url VARCHAR2(2000));
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_storage');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_Lex_URL');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_Lex_URL', 'BASIC_LEXER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_Lex_URL', 'whitespace', '_-');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_AUTO_FILTER');
END;
/
BEGIN
ctx_ddl.create_preference('KJC_TEST_INDEX_2_AUTO_FILTER','AUTO_FILTER');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout','120');
ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout_type','HEURISTIC');
END;
/
BEGIN
CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF');
END;
/
BEGIN
CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','DIRECTORY_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','DIRECTORY','MYHOME');
END;
/
INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
VALUES ('ABC-125', 'example.dat');
commit;
-- FILTER KJC_TEST_INDEX_2_AUTO_FILTER
-- NOPOPULATE
EXEC CTXSYS.CTX_OUTPUT.START_LOG ('TESTLOG.TXT')
CREATE INDEX KJC_TEST_INDEX_2_url1
ON KJC_TEST_INDEX_2(TEXT_CONTENT_URL)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
('DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF
LEXER KJC_TEST_INDEX_2_Lex_URL
STOPLIST CTXSYS.EMPTY_STOPLIST
STORAGE KJC_TEST_INDEX_2_storage
MEMORY 50M')
/
EXEC CTXSYS.CTX_OUTPUT.END_LOG
COLUMN token_text FORMAT A30
select distinct token_text
, token_type
, token_count
FROM dr$kjc_test_index_2_url1$I
order by token_text, token_type
/
-- check log:
COLUMN payload FORMAT A75
SELECT PAYLOAD
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE COMPONENT_NAME='CONTEXT_INDEX'
AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
ORDER BY TIMESTAMP;
commit;
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> CONNECT / AS SYSDBA
Connected.
SYS@XE_21.3.0.0.0>
SYS@XE_21.3.0.0.0>
SYS@XE_21.3.0.0.0> -- CREATE ORACLE DIRECTORY OBJECT MYHOME FOR OPERATING SYSTEM DIRECTORY c:\my_oracle_files:
SYS@XE_21.3.0.0.0> CREATE OR REPLACE DIRECTORY MYHOME as 'c:\my_oracle_files\'
2 /
Directory created.
SYS@XE_21.3.0.0.0> -- GRANT READ PRIVILEGES TO THE ORACLE DIRECTORY OBJECT:
SYS@XE_21.3.0.0.0> GRANT READ ON DIRECTORY MYHOME TO C##SCOTT
2 /
Grant succeeded.
SYS@XE_21.3.0.0.0>
SYS@XE_21.3.0.0.0> CONNECT C##SCOTT
Connected.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> DROP TABLE KJC_TEST_INDEX_2;
Table dropped.
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE KJC_TEST_INDEX_2
2 (assigned_id VARCHAR2(11),
3 text_content_url VARCHAR2(2000));
Table created.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_storage');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_2_storage', 'BASIC_STORAGE');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_TABLE_CLAUSE','tablespace USERS ');
4 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'K_TABLE_CLAUSE','tablespace USERS ');
5 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'R_TABLE_CLAUSE','tablespace USERS ');
6 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'N_TABLE_CLAUSE','tablespace USERS ');
7 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_storage', 'I_INDEX_CLAUSE','tablespace USERS ');
8 END;
9 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_Lex_URL');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_2_Lex_URL', 'BASIC_LEXER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_Lex_URL', 'whitespace', '_-');
4 END;
5 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_AUTO_FILTER');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 ctx_ddl.create_preference('KJC_TEST_INDEX_2_AUTO_FILTER','AUTO_FILTER');
3 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout','120');
4 ctx_ddl.set_attribute('KJC_TEST_INDEX_2_AUTO_FILTER', 'Timeout_type','HEURISTIC');
5 END;
6 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.DROP_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF');
3 END;
4 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> -- REPLACE URL_DATASTORE WITH DIRECTORY_DATASTORE AND SET DIRECTORY PARAMETER:
C##SCOTT@XE_21.3.0.0.0> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('KJC_TEST_INDEX_2_NETWORK_PREF','DIRECTORY_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE('KJC_TEST_INDEX_2_NETWORK_PREF','DIRECTORY','MYHOME');
4 END;
5 /
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> -- INSERT A FILE NAME THAT IS IN YOUR DIRECTORY:
C##SCOTT@XE_21.3.0.0.0> INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
2 VALUES ('ABC-125', 'example.dat');
1 row created.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> -- THE FOLLOWING PARAMETERS WERE REMOVED FROM THE INDEX CREATION BELOW:
C##SCOTT@XE_21.3.0.0.0> -- FILTER KJC_TEST_INDEX_2_AUTO_FILTER
C##SCOTT@XE_21.3.0.0.0> -- NOPOPULATE
C##SCOTT@XE_21.3.0.0.0> EXEC CTXSYS.CTX_OUTPUT.START_LOG ('TESTLOG.TXT')
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0> CREATE INDEX KJC_TEST_INDEX_2_url1
2 ON KJC_TEST_INDEX_2(TEXT_CONTENT_URL)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS
5 ('DATASTORE KJC_TEST_INDEX_2_NETWORK_PREF
6 LEXER KJC_TEST_INDEX_2_Lex_URL
7 STOPLIST CTXSYS.EMPTY_STOPLIST
8 STORAGE KJC_TEST_INDEX_2_storage
9 MEMORY 50M')
10 /
Index created.
C##SCOTT@XE_21.3.0.0.0> EXEC CTXSYS.CTX_OUTPUT.END_LOG
PL/SQL procedure successfully completed.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> COLUMN token_text FORMAT A30
C##SCOTT@XE_21.3.0.0.0> select distinct token_text
2 , token_type
3 , token_count
4 FROM dr$kjc_test_index_2_url1$I
5 order by token_text, token_type
6 /
TOKEN_TEXT TOKEN_TYPE TOKEN_COUNT
------------------------------ ---------- -----------
ASKING 0 1
COORDINATION 0 1
DOCUMENTS 0 1
DOMAIN 0 1
EXAMPLE 0 1
EXAMPLES 0 1
FOR 0 1
ILLUSTRATIVE 0 1
IN 0 1
INFORMATION 0 1
IS 0 1
LITERATURE 0 1
MAY 0 1
MORE 0 1
OR 0 1
PERMISSION 0 1
PRIOR 0 1
THIS 0 1
USE 0 1
WITHOUT 0 1
YOU 0 1
21 rows selected.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> -- check log:
C##SCOTT@XE_21.3.0.0.0> COLUMN payload FORMAT A75
C##SCOTT@XE_21.3.0.0.0> SELECT PAYLOAD
2 FROM V$DIAG_TRACE_FILE_CONTENTS
3 WHERE COMPONENT_NAME='CONTEXT_INDEX'
4 AND TIMESTAMP >= SYSTIMESTAMP - (1/(24*60)) -- or whenever you started
5 ORDER BY TIMESTAMP;
PAYLOAD
---------------------------------------------------------------------------
ksmddg: DGA destroyed
14-34-14 08/15/23 Oracle Text, 21.0.0.0.0
14-34-14 08/15/23 begin logging
14-34-14 08/15/23 populate index: C##SCOTT.KJC_TEST_INDEX_2_URL1
14-34-14 08/15/23 Using memory parameter 52428800
14-34-14 08/15/23 Begin document indexing
14-34-14 08/15/23 Begin writing index to database.
14-34-14 08/15/23 Errors reading documents: 0
14-34-14 08/15/23 Index data for 1 documents to be written to database
14-34-14 08/15/23 memory use: 412418
14-34-14 08/15/23 Start writing index to $I.
14-34-14 08/15/23 Begin sorting the inverted list.
14-34-14 08/15/23 End sorting the inverted list.
14-34-14 08/15/23 Writing index data ($I) to database.
14-34-14 08/15/23 Using direct path writes for "C##SCOTT"."DR$KJC_TEST_INDE
X_2_URL1$I"
14-34-15 08/15/23 Inserting using direct path loading
14-34-15 08/15/23 Wrote 21 rows (0 big rows) to $I.
14-34-15 08/15/23 Finish writing index to $I.
14-34-15 08/15/23 Writing index data ($K) to database.
14-34-15 08/15/23 Wrote 1 rows to $K.
14-34-15 08/15/23 index data written to database.
14-34-15 08/15/23 possibly start in-memory indexing again
14-34-15 08/15/23 Using memory parameter 52428800
14-34-15 08/15/23 End of document indexing. 1 documents indexed.
14-34-15 08/15/23 Creating Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL
1$X"
14-34-15 08/15/23 Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL1$X" crea
ted
14-34-15 08/15/23 Creating Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL
1$KD"
14-34-15 08/15/23 Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL1$KD" cre
ated
14-34-15 08/15/23 Creating Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL
1$KR"
14-34-15 08/15/23 Oracle index "C##SCOTT"."DR$KJC_TEST_INDEX_2_URL1$KR" cre
ated
14-34-15 08/15/23 log
14-34-15 08/15/23 logging halted
32 rows selected.
C##SCOTT@XE_21.3.0.0.0>
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
[Updated on: Tue, 15 August 2023 16:39] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Apr 27 10:23:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
