问题现象：数据库库出现ORA-20000错误：

Errors in file /home/oracle/admin/zjindex/bdump/zjindex2_j002_615322.trc: ORA-12012: error on auto execute of job 8887 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 1338 ORA-06512: at "SYS.DBMS_SPACE", line 1554

分析过程：

1、定位查看JOB信息，确认由于AUTO_SPACE_ADVISOR_JOB出了问题

SQL> select job_name,state,run_count,failure_count from dba_scheduler_jobs;

JOB_NAME                       STATE            RUN_COUNT FAILURE_COUNT

------------------------------ --------------- ---------- ------------- PURGE_LOG                      SCHEDULED             1296             0 FGR$AUTOPURGE_JOB              DISABLED                 0             0 GATHER_STATS_JOB               SCHEDULED             1108             0 AUTO_SPACE_ADVISOR_JOB         SCHEDULED             1108           872 RLM$EVTCLEANUP                 SCHEDULED            31033             0 RLM$SCHDNEGACTION              SCHEDULED            32308             0 ADV_SEGMENTADV_1564578         SUCCEEDED                1             0 ADV_SEGMENTADV_5868170         SUCCEEDED                1             0 ADV_SEGMENTADV_4100857         SUCCEEDED                1             0

9 rows selected

SQL>

---------------------------------------------------------------------------------------------------------------------------------

通过查询dba_auto_segadv_ctl表获得被删除的表空间 SQL> select tablespace_name from dba_auto_segadv_ctl   2  where tablespace_name not in (select tablespace_name from dba_tablespaces)   3  /

TABLESPACE_NAME

------------------------------ TZQ

 2、原因：找不到表空间返回的错误

Metalink说是Oracle bug,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.AUTO_SPACE_ADVISOR_JOB。  由于TZQ表空间已经被删除，在运行AUTO_SPACE_ADVISOR_JOB时还会对它进行段建议。导致了ORA-20000错误发生。Internal BUG:4707226

3、解决方法：

※Bug 4707226 will be fixed in release 10.2.0.4-----------补丁升级 ※通过手动删除DBA_AUTO_SEGADV_CTL里表空间记录-------------手动维护  4、验证方法：  DELETE FROM dba_auto_segadv_ctl where tablespace_name not in (select tablespace_name from dba_tablespaces)     执行exec dbms_space.auto_space_advisor_job_proc验证  

本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/394442，如需转载请自行联系原作者