Skip to Main Content
  • Questions

  • CONTEXT Index not working after patching or Daylight Savings Change

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: January 07, 2025 - 8:15 pm UTC

Last updated: January 22, 2025 - 1:45 am UTC

Version: 12c

Viewed 100+ times

You Asked

We have a context index that is SYNCing every 6 seconds. Here is the DDL for the index:

CREATE INDEX "SSS"."QUOTE_NUMBER_CONTEXT" ON "SSS"."QUOTE" ("NUMBER_")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('SYNC (EVERY "SYSDATE+1/14400")');

This was working perfectly for many months.

We then did patching on the database and server and there was a daylight savings change. Now, the scheduler is showing that the index is still SYNCing every 6 seconds, but any new data on the table is NOT being updated on the index until exactly 1 hour later! We have no idea why this is happening and we have checked all the parameters, with no luck.

Thanks.

and Connor said...

A possible cause is that the scheduler can have its own timezone/timestamp information


Rating

  (1 rating)

Comments

CONTEXT Index issue

Mike, January 14, 2025 - 8:10 pm UTC

Hi

We have one database where the CONTEXT index is working and another database where it is NOT working.

I noticed that the timezone for the scheduler was different on the 2 environments, so I updated the scheduler time zone and dropped / recreated the index, but it did not work.

The DBTIMEZONE for each database is the same on both databases:

SQL> SELECT dbtimezone FROM DUAL;

DBTIMEZONE
------------------
-06:00

The issue seems to be with the NEXT_RUN_DATE on the ALL_SCHEDULER_JOBS table. On the database that is working, the NEXT_RUN_DATE looks like this:

14-JAN-25 02.06.50.000000000 PM -06:00

However, on the database where it is NOT working, it seems like the time zone is wrong:

14-JAN-25 01.07.21.000000000 PM -07:00

Does anyone know where that TIME ZONE is coming from for the last_start_date and next_run_date on the all_scheduler_jobs table for the CONTEXT INDEX?

Thanks. Mike
Connor McDonald
January 22, 2025 - 1:45 am UTC

What does this give?

select dbms_scheduler.stime from dual;
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.