My Oracle Support Banner

Index rebuild does NOT run online when the PL/SQL procedure is executed by DBMS_SCHEDULER (Doc ID 2662097.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 18.5.0.0.0 and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

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)

Changes

 Upgraded from 11.2

Cause

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.