Index rebuild does NOT run online when the PL/SQL procedure is executed by DBMS_SCHEDULER
(Doc ID 2662097.1)
Last updated on JANUARY 04, 2021
Applies to:Oracle Database - Enterprise Edition - Version 18.104.22.168.0 and later
Information in this document applies to any platform.
In an environment for 18.6 index rebuild online made form dbms_scheduler job is not really online and blocks dml.
Also noticed that SYS_JOURNAL_NNNN table is not created.
From this point of view it is the sign the operation is not actually online.
Index rebuild online made from ordinal sqlplus session is made online (i.e do not block dml and SYS_JOURNAL_NNNN table is created).
In 11.2 index rebuild online works as expected from both JOB and ordinal sqlplus session.
According to bellow documents the temporary table SYS_JOURNAL is used when the index is rebuilt online. On the Oracle Database 19c the SYS_JOURNAL is recorded in the trace files when the PL/SQL procedure or the scheduler JOB is used. For the Oracle Database 18c the SYS_JOURNAL temporary table is recorded in the trace files when the PL/SQL procedure is executed from SQL*Plus, but the SYS_JOURNAL temporary table is NOT recorded in the trace files when the scheduler job is run.
How To Find Out Temporary Table Sys_journal_xxxxx Is Created By Rebuilding Which Index (Doc ID 1324941.1)
Rebuild Index Online Causes Extract/Replicat Abend (Doc ID 1348226.1)
Upgraded from 11.2
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document