ITPub博客

首页 > 数据库 > Oracle > [20190920]Asynchronous Global Index Maintenance and Recycle Bin.txt

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

原创 Oracle 作者:lfree 时间:2019-09-20 20:53:01 0 删除 编辑

[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软件的复杂性.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2657798/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292785