[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軟體的複雜性.