ORACLE10G ORA-20000問題處理詳解

問題現象：資料庫庫出現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驗證