AUTO_SPACE_ADVISOR_JOB ERROR ORA-20000

错误信息如下﹕

ORA-12012: error on auto execute of job 8898

ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid

ORA-06512: at "SYS.PRVT_ADVISOR", line 1624

ORA-06512: at "SYS.DBMS_ADVISOR", line 186

ORA-06512: at "SYS.DBMS_SPACE", line 1344

ORA-06512: at "SYS.DBMS_SPACE", line 1560

这是oracle的一个bug

产生原因﹕

CauseThe problem is when the tablespace is created the statistics are captured for this tablespace. When the tablespace is
dropped the segments for which the segstats are collected continue to reference the dropped tablespace.

If you manually remove the tablespace candidate from DBA_AUTO_SEGADV_CTL, it will be referenced again during the next run and continue to fail.

解决方法﹕

用下面的方法选出在dba_auto_segadv_ctl中存在但在dba_tablespaces中不存在的表空间

SQL> select tablespace_name from dba_auto_segadv_ctl

2 minus

3 select tablespace_name from dba_tablespaces;

建立上面的SQL语句中执行结果的表空间

create tablespace tablespacename dataflile ‘path/datafilename’ size 1M;

再次执行

SQL> exec dbms_space.auto_space_advisor_job_proc;

成功。

当然你也可以通过打patch来解决。

ITPub博客

AUTO_SPACE_ADVISOR_JOB ERROR ORA-20000

最新文章