ORCL(3):Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_j000_10274.trc: ORA-12012: error on auto execute of job "SYS"."PMO_DEFERRED_GIDX_MAINT_JOB" ORA-38301: can not perform DDL/DML over objects in Recycle Bin ORA-06512: at "SYS.DBMS_PART", line 131 ORA-06512: at "SYS.DBMS_PART", line 131 ORA-06512: at "SYS.DBMS_PART", line 120 ORA-06512: at line 1The highlighted line indicates that the error relates to the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB, which was introduced in the Oracle database 12.1 release.
It is responsible for cleaning up indexes after drop/truncate operations: link.
I delved into the issue and found that the job SYS.PMO_DEFERRED_GIDX_MAINT_JOB did not ignore objects in the Recycle Bin.
Here is a short test case to demonstrate that:
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production PL/SQL Release 12.2.0.1.0 - Production CORE 12.2.0.1.0 Production TNS for Linux: Version 12.2.0.1.0 - Production NLSRTL Version 12.2.0.1.0 - Production SQL> create table t(x) 2 partition by range(x) 3 ( 4 partition values less than(maxvalue) 5 ) 6 as 7 select 1 8 from dual; Table created. SQL> SQL> create index t_i on t(x); Index created. SQL> SQL> alter table t truncate partition for(1) update indexes; Table truncated. SQL> SQL> select status, orphaned_entries 2 from ind 3 where index_name = 'T_I'; STATUS ORP -------- --- VALID YES SQL> SQL> drop table t; Table dropped.Now I run the job SYS.PMO_DEFERRED_GIDX_MAINT_JOB manually as if it was running automatically:
SQL> select run_count, failure_count, state from user_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
RUN_COUNT FAILURE_COUNT STATE
---------- ------------- --------------------
4 0 SCHEDULED
SQL> exec dbms_scheduler.run_job( 'PMO_DEFERRED_GIDX_MAINT_JOB', false)
PL/SQL procedure successfully completed.
SQL> select run_count, failure_count, state from user_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
RUN_COUNT FAILURE_COUNT STATE
---------- ------------- --------------------
5 1 SCHEDULED
The job SYS.PMO_DEFERRED_GIDX_MAINT_JOB calls the DBMS_PART.CLEANUP_GIDX_INTERNAL procedure.The same ORA-38301 error can be raised if I call the DBMS_PART.CLEANUP_GIDX procedure:
SQL> exec DBMS_PART.CLEANUP_GIDX (user) BEGIN DBMS_PART.CLEANUP_GIDX (user); END; * ERROR at line 1: ORA-38301: can not perform DDL/DML over objects in Recycle Bin ORA-06512: at "SYS.DBMS_PART", line 131 ORA-06512: at "SYS.DBMS_PART", line 131 ORA-06512: at "SYS.DBMS_PART", line 120 ORA-06512: at "SYS.DBMS_PART", line 193 ORA-06512: at line 1The problem here is that the automatic job stops further processing on any error.
It means that we may end up having lots of indexes requiring cleanup that are not processed automatically and have to undergone manual actions to reset their state.
tl;dr. Although the Asynchronous Global Index Maintenance feature is quite useful and can greatly speedup the partition maintenance operations TRUNCATE PARTITION and DROP PARTITION, it still does not ignore objects in the Recycle Bin.
Therefore, the automatic maintenance job PMO_DEFERRED_GIDX_MAINT_JOB may fail and does not process all of the indexes that require cleanup.
