Oracle Asynchronous Global Index Maintenance jobs for DROP and TRUNCATE Partition in Oracle Database 12c Release 1

 
This new feature in Oracle 12C is as default always on. Each TRUNCATE or DROP commands performed on a partition automatically triggers asynchronous global index maintenance. It means that you don’t need to wait for global INDEX maintenance 

The column DBA_INDEXES.ORPHANED_ENTRIES shows that Oracle is aware that the index may have keys referring to dropped partitions. There is a pre-seeded daily job that tidies them up; by default, it runs every night from 22:00. 

Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time.
 
Delay global index maintenance to off-peak hours without affecting index availability, and reduce and truncate partition and sub-partition maintenance operations faster and with fewer resources at the point in time for partition maintenance operations.
 
When combined with the update index clause, the DROP partition and the TRUNCATE partition command will result in metadata index maintenance. This feature is only used for heap tables and does not support object types, domain indexes, or tables owned by SYS.

For backward compatibility you still need to specify UPDATE INDEXES clause.

Limitations of asynchronous global index maintenance:

Only performed on heap tables

No support for tables with object types

No support for tables with domain indexes

Not performed for the user SYS

Asynchronous cleanup orphans in an index can be done:

Automatically by Oracle job SYS.PMO_DEFERRED_GIDX_MAINT_JOB

Manually

just run above job SYS.PMO_DEFERRED_GIDX_MAINT_JOB

run procedure DBMS_PART.CLEANUP_GIDX

run sql statement ALTER INDEX REBUILD [PARTITION] – the same like in previous releases

run sql statement ALTER INDEX [PARTITION] COALESCE CLEANU

 select job_name , start_date,enabled,state,comments

  2  from dba_scheduler_jobs

  3  where job_name ='PMO_DEFERRED_GIDX_MAINT_JOB';

JOB_NAME             START_DATE           ENABL STATE           COMMENTS

-------------------- -------------------- ----- --------------- --------------------

PMO_DEFERRED_GIDX_MA 29-JUN-13 02.00.00.6 TRUE  SCHEDULED       Oracle defined autom

INT_JOB              00000 AM US/CENTRAL                                                            atic index cleanup f

                                                                                                                                    or partition mainten

                                                                                                                                        ance operations with

                                                                                                                                 deferred global ind

                                                                                                                                    ex maintenance

Tradional Way : 

________________________

ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;

ALTER TABLE t1 DROP PARTITION part_2014 UPDATE INDEXES;

To Run Job manually 

________________________

select enabled,run_count from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';

exec dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB')

Manually trigger the index maintenance.

________________________

EXEC DBMS_PART.cleanup_gidx(USER, 't1');

To Enable Logging 

________________________

By default run history for this job wont be visible . We need  to force enable its logging as per Doc 2506878.1 

sqlplus / as sysdba

SQL> select job_name from dba_Scheduler_jobs where job_class = 'SCHED$_LOG_ON_ERRORS_CLASS'

exec dbms_scheduler.set_attribute('<JOB_NAME>', 'logging_level',DBMS_SCHEDULER.LOGGING_FULL);

  

Disable Job 

________________________

This was main reason for me  to write Blog on this . We were facing  locking issue in most of database  so we planned to disable  this job 

select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'SPMO_DEFERRED_GIDX_MAINT_JOB';

exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB')

EXEC SYS.DBMS_SCHEDULER.DISABLE ('PMO_DEFERRED_GIDX_MAINT_JOB'); 

In case we are planing to disable all scheduled jobs we can use below  : 

  col ATTRIBUTE_NAME for a30

  col VALUE for a60

  set lines 180

  set pages 999

  select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

  exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

Reference: 

1) New Oracle Provided 12C Jobs are Not Showing any Execution Run History Details (Doc ID 2506878.1) 

2) https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107