Home » Server Options » Text & interMedia » where to check context index sync status (oracle 12c)
where to check context index sync status [message #666505] Thu, 09 November 2017 04:29 Go to next message
mvrkr44
Messages: 108
Registered: December 2012
Senior Member

On : 11.2.0.4 version, RDBMS

Need to get the last execution time and time to took for syncing the CONTEXT index

create table test1(names varchar2(1000));
create index idx_names on test1(names) indextype is ctxsys.context;
insert into test1 values('test record');
commit;

after inserting record i am running the below block.
begin
ctx_ddl.sync_index('idx_names');
end;
/
we are running the above index sync block every one hour .
Now we want to see the Context Index sync status & how much time took to execute the sync index and how many records also?Is there any table /view to check all these tables?

Re: where to check context index sync status [message #666525 is a reply to message #666505] Fri, 10 November 2017 20:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
You can use a log as demonstrated below.

SCOTT@orcl_12.1.0.2.0> create table test1(names varchar2(1000));

Table created.

SCOTT@orcl_12.1.0.2.0> create index idx_names on test1(names) indextype is ctxsys.context;

Index created.

SCOTT@orcl_12.1.0.2.0> insert into test1 values('test record');

1 row created.

SCOTT@orcl_12.1.0.2.0> commit;

Commit complete.

SCOTT@orcl_12.1.0.2.0> begin
  2    CTX_OUTPUT.START_LOG('SYNCINDEX.LOG');
  3    ctx_ddl.sync_index('idx_names');
  4    CTX_OUTPUT.END_LOG;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> HOST TYPE C:\app\baboehme\product\12.1.0\dbhome_2\ctx\log\SYNCINDEX.LOG
Oracle Text, 12.1.0.2.0
18-31-18 11/10/17 begin logging
18-31-18 11/10/17 Beginning sync_index of index IDX_NAMES
18-31-18 11/10/17 Obtaining sync lock
18-31-18 11/10/17 Obtained sync lock
18-31-18 11/10/17 process dr$waiting table: SCOTT.IDX_NAMES
18-31-18 11/10/17 sync index: SCOTT.IDX_NAMES
18-31-18 11/10/17 Begin document indexing
18-31-18 11/10/17 Begin writing index to database.
18-31-18 11/10/17 Errors reading documents: 0
18-31-18 11/10/17 Index data for 1 documents to be written to database
18-31-18 11/10/17    memory use: 405260
18-31-18 11/10/17 Start writing index to $I.
18-31-18 11/10/17 Begin sorting the inverted list.
18-31-19 11/10/17 End sorting the inverted list.
18-31-19 11/10/17 Writing index data ($I) to database.
18-31-19 11/10/17 Flush size less than 16MB, Conventional Inserts
18-31-19 11/10/17 Wrote 2 rows (0 big rows) to $I.
18-31-19 11/10/17 Finish writing index to $I.
18-31-19 11/10/17 Writing index data ($K) to database.
18-31-19 11/10/17 Wrote 1 rows to $K.
18-31-19 11/10/17 Writing index data ($R) to database.
18-31-19 11/10/17 Locking base table rows.
18-31-19 11/10/17    index data written to database.
18-31-19 11/10/17 possibly start in-memory indexing again
18-31-19 11/10/17 End of document indexing. 1 documents indexed.
18-31-19 11/10/17 process dr$waiting table: SCOTT.IDX_NAMES
18-31-19 11/10/17 Completed sync_index of index IDX_NAMES
18-31-19 11/10/17 log
18-31-19 11/10/17 logging halted
Re: where to check context index sync status [message #666553 is a reply to message #666525] Sun, 12 November 2017 23:28 Go to previous message
mvrkr44
Messages: 108
Registered: December 2012
Senior Member
Thanks Barbara Boehmer .
Previous Topic: Matching query
Goto Forum:
  


Current Time: Mon Nov 20 07:50:17 CST 2017

Total time taken to generate the page: 0.01350 seconds