[20190920]Asynchronous Global Index Maintenance and Recycle Bin.txt

[20190920]Asynchronous Global Index Maintenance and Recycle Bin.txt --//oracle 12c删除分区时,索引可以延后清理.如果整个分别表删除了后台SYS.PMO_DEFERRED_GIDX_MAINT_JOB调度会报ORA-38301错误!! --//做一个例子演示看看: 1.环境： SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 create table t partition by range (d) (   partition t_q1_2019 values less than (to_date('2019-04-01','yyyy-mm-dd')),   partition t_q2_2019 values less than (to_date('2019-07-01','yyyy-mm-dd')),   partition t_q3_2019 values less than (to_date('2019-10-01','yyyy-mm-dd')),   partition t_q4_2019 values less than (to_date('2020-01-01','yyyy-mm-dd')) ) as select rownum as n, to_date('2019-01-01','yyyy-mm-dd') + rownum/(1e5/364) as d, rpad('*',10,'*') as p from dual connect by level <= 1e5; create index i_t_n on t (n) ; alter table t drop partition t_q1_2019 update indexes; 2.测试： SCOTT@test01p> select status, orphaned_entries from dba_indexes  where index_name = 'I_T_N'; STATUS ORP ------ --- VALID  YES --//orphaned_entries=YES. SCOTT@test01p> drop table t; Table dropped. --//oracle会定时调用job SYS.PMO_DEFERRED_GIDX_MAINT_JOB维护索引，手工执行看看. --//以sys用户登录执行: SYS@test01p> select run_count, failure_count, state from user_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';  RUN_COUNT FAILURE_COUNT STATE ---------- ------------- --------------------        113             0 SCHEDULED SYS@test01p> exec dbms_scheduler.run_job( 'PMO_DEFERRED_GIDX_MAINT_JOB', false) PL/SQL procedure successfully completed. SYS@test01p> select run_count, failure_count, state from user_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';  RUN_COUNT FAILURE_COUNT STATE ---------- ------------- --------------------        114             1 SCHEDULED --//调用失败.查看alert.log文件出现: 2019-09-20T20:38:50.807047+08:00 TEST01P(3):performing DML/DDL operation over object in bin. 2019-09-20T20:38:50.903052+08:00 TEST01P(3):Errors in file D:\APP\ORACLE\diag\rdbms\test\test\trace\test_j000_2224.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 1 2019-09-20T20:41:42.462865+08:00 TEST01P(3):performing DML/DDL operation over object in bin. 2019-09-20T20:47:02.836189+08:00 --//D:\APP\ORACLE\diag\rdbms\test\test\trace\test_j000_2224.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 1 --//手工执行DBMS_PART.CLEANUP_GIDX ('SCOTT')看看: SYS@test01p> exec DBMS_PART.CLEANUP_GIDX ('SCOTT'); BEGIN DBMS_PART.CLEANUP_GIDX ('SCOTT'); 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 1 SYS@test01p> host oerr ora 38301 38301, 00000, "can not perform DDL/DML over objects in Recycle Bin" // *Cause: Tried to perform DDL or DML operation on Recycle Bin object. // *Action: DDL or DML operations are not permitted on Recycle Bin objects. 3.总结: --//这个应该算是bug,只能说明oracle软件的复杂性.