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 Go to next message
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'
2.  Is there a way to determine when a given document was last indexed?

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #688028 is a reply to message #688026] Mon, 14 August 2023 15:32 Go to previous messageGo to next message
kjcook
Messages: 31
Registered: January 2016
Member
So I tried this.  And I am hung up on the 2nd line:  "DROP INDEX kjc_test_index_url1 FORCE;"
It has been sitting on it for about 14 minutes.  My guess is it will time out due to a lock?
Re: How to determine last tiime Content was indexed [message #688029 is a reply to message #688028] Mon, 14 August 2023 15:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Actually, when the table is dropped, it should drop the index automatically along with the table.  So, you can just skip that step.  If there is a way that you can abort it, then I would do that, then start over without that.  I don't know what you are running it on.  I hope you are using a test database and not your production.  If running from SQL*Plus as I have been you can kill the session or just press ctrl and c or close the window.
Re: How to determine last tiime Content was indexed [message #688030 is a reply to message #688029] Mon, 14 August 2023 15:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Here is the output:

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 #688032 is a reply to message #688030] Mon, 14 August 2023 16:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since everything but the index creation statement went well, I would try re-running just the index creation, but without the parallel 5.  It looks like it is having a problem with perceiving that there is already an index or it is already locked.  It may be due to some sort of bug that causes it to see the parallel portions as conflicting.  It may also be due to leftover pieces of domain index tables from previous dopping with force or aborting due to timeout or whatever.  I have seen that happen before.  You can run a log to try to get more details.  Try this:


EXEC CTX_OUTPUT.START_LOG ('TESTLOG.TXT')

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');

EXEC CTX_OUTPUT.END_LOG 

SELECT PAYLOAD
FROM   V$DIAG_TRACE_FILE_CONTENTS 
WHERE  COMPONENT_NAME='CONTEXT_INDEX'
AND    TIMESTAMP >= SYSTIMESTAMP - (1/24) -- or whenever you started
ORDER  BY TIMESTAMP
/


Re: How to determine last tiime Content was indexed [message #688033 is a reply to message #688032] Mon, 14 August 2023 16:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You could also run the following to see if there is a conflict with the same name with something else.


select owner, object_type from all_objects where object_name = 'KJC_TEST_INDEX_URL1';
Re: How to determine last tiime Content was indexed [message #688034 is a reply to message #688033] Mon, 14 August 2023 17:22 Go to previous messageGo to next message
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 #688035 is a reply to message #688034] Mon, 14 August 2023 17:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You need to check whether there is another any type of object, not just an index, with perhaps even a case-insensitive name.  So, you need to:

Select owner, object_type from all_objects where UPPER(object_name) = 'KJC_TEST_INDEX_URL1';


It would be even better if you can select from dba_objects;
Re: How to determine last tiime Content was indexed [message #688036 is a reply to message #688035] Mon, 14 August 2023 17:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I have had this happen to me before in other versions.  I was trying to see if I could recreate in what i have, but have not been able to.

Here is one of many examples on the web of someone with the same problem and the extreme measures taken to remove all of the leftover pieces that were not properly purged in the drop, so that they can be recreated without conflict.

https://stackoverflow.com/questions/32866771/not-able-to-drop-and-recreate-the-oracle-text-index

There does seem to be some sort of a lag such that if you wait long enough the cleanup from the drop finishes and you can then recreate.

[Updated on: Mon, 14 August 2023 17:47]

Report message to a moderator

Re: How to determine last tiime Content was indexed [message #688037 is a reply to message #688036] Mon, 14 August 2023 17:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
In the past, I have worked around the problem by creating entirely different names for all tables and indexes and such involved, so that there is no conflict.
Re: How to determine last tiime Content was indexed [message #688038 is a reply to message #688037] Mon, 14 August 2023 18:28 Go to previous messageGo to next message
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') ;

And here is the output:

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
The DR$KJC_TEST_INDEX_1_URL1$ tables are empty.

[Updated on: Mon, 14 August 2023 18:29]

Report message to a moderator

Re: How to determine last tiime Content was indexed [message #688039 is a reply to message #688038] Mon, 14 August 2023 18:42 Go to previous messageGo to next message
kjcook
Messages: 31
Registered: January 2016
Member
SELECT PAYLOAD
FROM   V$DIAG_TRACE_FILE_CONTENTS 
WHERE  COMPONENT_NAME='CONTEXT_INDEX'
AND    TIMESTAMP >= SYSTIMESTAMP - (1/24) -- or whenever you started
ORDER  BY TIMESTAMP;
Returns the following rows:

17:39:56 08/14/23 Oracle Text, 19.0.0.0.0
17:39:56 08/14/23 begin logging
17:39:56 08/14/23 check text datastore access
17:39:56 08/14/23 check pdb lockdown
17:39:57 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X"
17:39:57 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X" created
17:39:57 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD"
17:39:57 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD" created
17:39:57 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR"
17:39:57 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR" created
17:39:57 08/14/23 log
17:39:57 08/14/23 logging halted
Re: How to determine last tiime Content was indexed [message #688040 is a reply to message #688039] Mon, 14 August 2023 18:49 Go to previous messageGo to next message
kjcook
Messages: 31
Registered: January 2016
Member
 
EXEC ctxsys.CTX_OUTPUT.START_LOG ('TESTLOG.TXT')

ALTER INDEX KJC_TEST_INDEX_1_url1 REBUILD ONLINE;

EXEC CTX_DDL.OPTIMIZE_INDEX('KJC_TEST_INDEX_1_url1', 'FULL');

EXEC ctxsys.CTX_OUTPUT.END_LOG

-- check for errors:
SELECT * FROM CTX_USER_INDEX_ERRORS;

-- 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;


Returns:

17:47:35 08/14/23 Oracle Text, 19.0.0.0.0
17:47:35 08/14/23 begin logging
17:47:35 08/14/23
17:47:36 08/14/23 resume index: MI.KJC_TEST_INDEX_1_URL1
17:47:36 08/14/23 Using memory parameter 67108864
17:47:36 08/14/23 Begin document indexing
17:47:36 08/14/23 Begin writing index to database.
17:47:36 08/14/23 Errors reading documents: 0
17:47:36 08/14/23 Index data for 1 documents to be written to database
17:47:36 08/14/23    memory use: 439199
17:47:36 08/14/23 Start writing index to $I.
17:47:36 08/14/23 Begin sorting the inverted list.
17:47:36 08/14/23 End sorting the inverted list.
17:47:36 08/14/23 Writing index data ($I) to database.
17:47:36 08/14/23 Inserting using direct path loading
17:47:36 08/14/23 Wrote 203 rows (0 big rows) to $I.
17:47:36 08/14/23 Finish writing index to $I.
17:47:36 08/14/23 Writing index data ($K) to database.
17:47:36 08/14/23 Wrote 1 rows to $K.
17:47:36 08/14/23    index data written to database.
17:47:36 08/14/23 possibly start in-memory indexing again
17:47:36 08/14/23 Using memory parameter 67108864
17:47:36 08/14/23 End of document indexing. 1 documents indexed.
17:47:36 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X"
17:47:36 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$X" created
17:47:36 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD"
17:47:36 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KD" created
17:47:36 08/14/23 Creating Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR"
17:47:36 08/14/23 Oracle index "MI"."DR$KJC_TEST_INDEX_1_URL1$KR" created
17:47:36 08/14/23 Locking table KJC_TEST_INDEX_1
17:47:36 08/14/23 Processing pending DMLs
17:47:36 08/14/23 Trying to get optimize xlock.
17:47:36 08/14/23 Got optimize xlock.
17:47:36 08/14/23 process $N for optimize: MI.KJC_TEST_INDEX_1_URL1
17:47:36 08/14/23 Finish processing $N for optimize.
17:47:36 08/14/23 optimize full: MI.KJC_TEST_INDEX_1_URL1
17:47:36 08/14/23 starting new optimization from beginning of $I table
17:47:36 08/14/23 0 invalid docids read from $N table
17:47:36 08/14/23 full optimize initialization complete
17:47:36 08/14/23 starting $I row optimization
17:47:36 08/14/23 deleting rows from $I table
17:47:36 08/14/23 deleted 0 rows from $I table
17:47:36 08/14/23 $I table optimize full MI.KJC_TEST_INDEX_1_URL1 complete
17:47:36 08/14/23 process $N for optimize: MI.KJC_TEST_INDEX_1_URL1
17:47:36 08/14/23 Finish processing $N for optimize.
17:47:36 08/14/23 Trying to release optimize lock.
17:47:36 08/14/23 Released optimize lock.
17:47:36 08/14/23 MI.KJC_TEST_INDEX_1_URL1 FULL Optimize Index Complete
17:47:37 08/14/23 log
17:47:37 08/14/23 logging halted
Re: How to determine last tiime Content was indexed [message #688041 is a reply to message #688040] Mon, 14 August 2023 18:58 Go to previous messageGo to next message
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:


    MCP-9395
    LST-1001
    WIP-5
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;
But they didn't show up, why?  What is wrong?

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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;
Here is the output

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 #688045 is a reply to message #688043] Tue, 15 August 2023 03:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It looks like the remaining problem may be a timeout issue.  You have the timeout parameter of your url_datastore set to 10 seconds.  It would be good to increase that.  However, apparently url_datastore is deprecated and it just allows a value for backwards compatibility, but ignores it.  So, you need to switch to network_datastore, then you can set the timeout to the maximum of 3600 seconds.  The network_datastore may require some additional things.  Please see the following explanation and example in the 19c online docucmentation. You will need to click on datastore types, then network_datastore.

  https://docs.oracle.com/en/database/oracle/oracle-database/19/ccref/oracle-text-indexing-elements.html#GUID-BFB3DB3F-0D1B-4B03-8581-E 395DEDDB636

[Updated on: Tue, 15 August 2023 03:18]

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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

Here is the output.

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 #688049 is a reply to message #688048] Tue, 15 August 2023 09:07 Go to previous messageGo to next message
kjcook
Messages: 31
Registered: January 2016
Member
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?

[Updated on: Tue, 15 August 2023 09:08]

Report message to a moderator

Re: How to determine last tiime Content was indexed [message #688050 is a reply to message #688049] Tue, 15 August 2023 10:57 Go to previous messageGo to next message
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
Here is the output


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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;
/
You need to run this to cause it to take effect:

ALTER INDEX KJC_TEST_INDEX_2_url1 REBUILD ONLINE
  PARAMETERS ('REPLACE DATASTORE  KJC_TEST_INDEX_2_NETWORK_PREF')
/
There may still be other issues, but try this first.

[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 Go to previous messageGo to next message
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;
Here are the results of the script:

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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #688057 is a reply to message #688056] Tue, 15 August 2023 14:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You are right.  You need a directory_datastore.  I remembered that your files were not stored in your table, but, since you were using a url_datastore, I incorrectly assumed they were on the web.  I see now they are in files on your server.  I would have thought you need a file_datastore, but i see now that file_datastore is deprecated and the replacement is directory_datastore.  So, first you need to switch to directory_datastore and get that working with one or more directory objects, then deal with the problem of creating new objects.  You will either need to arrange for everything to be stored in existing directories or have some process to create new Oracle directory objects that correspond to the operating system directories.  There may be an option to create a privilege at a high level of directories that applies to sub-directories.

I just need one clarification.  You were using things that looked like typical url paths.  Are your files also accessible via the internet from outside of your own server or intranet?
Re: How to determine last tiime Content was indexed [message #688059 is a reply to message #688057] Tue, 15 August 2023 15:24 Go to previous messageGo to next message
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 #688060 is a reply to message #688059] Tue, 15 August 2023 15:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Using the directory_datastore would be easier than what you are doing.  I have already got a script written and am testing, trying to work out the bugs.  I believe I am real close and just missing something simple, like a change in a name of something.  I am not getting any errors, but not getting any tokens either.  You can work in one direction if you like and I can work in the other and if I can get it working and post it, then you may wish to reconsider.
Re: How to determine last tiime Content was indexed [message #688061 is a reply to message #688060] Tue, 15 August 2023 15:38 Go to previous messageGo to next message
kjcook
Messages: 31
Registered: January 2016
Member
I trust you.  You are the guru.  The reason I am concerned with the directory_datastore, based on my very very limited knowledge, I have 1000s of directories and we add a new directories every week.  I am ready to try anything though.  Thank you for sticking with me.
Re: How to determine last tiime Content was indexed [message #688063 is a reply to message #688061] Tue, 15 August 2023 16:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Update:  

I have a script running without errors and producing tokens, but with all the other preferences used in the index parameters temporarily removed.  I just need to add them back one at a time and retest in order to figure out which one is in conflict and resolve it.
Re: How to determine last tiime Content was indexed [message #688064 is a reply to message #688063] Tue, 15 August 2023 16:36 Go to previous messageGo to previous message
Barbara Boehmer
Messages: 9077
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
/
-- RUN AS YOU HAVE BEEN:
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;
/
-- REPLACE URL_DATASTORE WITH DIRECTORY_DATASTORE AND SET DIRECTORY PARAMETER:
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 A FILE NAME THAT IS IN YOUR DIRECTORY:
INSERT into KJC_TEST_INDEX_2 (assigned_id, text_content_url)
  	    VALUES ('ABC-125', 'example.dat');
commit;
-- THE FOLLOWING PARAMETERS WERE REMOVED FROM THE INDEX CREATION BELOW:
--      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;
-- HERE IS THE RESULT OF MY TEST RUN:
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

Previous Topic: Oracle Text Search - How to drop DR$ tables and indexes
Next Topic: delete from ctxsys.dr$delete - statement not using bind variables: how to contain of limit?
Goto Forum:
  


Current Time: Thu Feb 22 05:48:50 CST 2024