ORA-12012: error on auto execute of job ORA-20000: ORA-20000

Oracle10g中每天晚上10点会进行自动搜集统计信息，数据库准时报下面这个错，已经忍它很久了，虽然对数据库没什么大影响，但是每天查日志看见也很不爽：

Tue Dec 19 22:00:03 2006
Errors in file /oracle/admin/shdt2/bdump/shdt2_j000_8466.trc:
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/admin/shdt2/bdump/shdt2_j000_8466.trc:

*** ACTION NAME:(AUTO_SPACE_ADVISOR_JOB) 2006-12-19 22:00:03.457
*** MODULE NAME:(DBMS_SCHEDULER) 2006-12-19 22:00:03.457
*** SERVICE NAME:(SYS$USERS) 2006-12-19 22:00:03.457
*** CLIENT ID:() 2006-12-19 22:00:03.457
*** SESSION ID:(487.15519) 2006-12-19 22:00:03.457
*** 2006-12-19 22:00:03.457
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

错误重现：

sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Dec 20 09:03:48 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;

*
ERROR at line 1:
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
ORA-06512: at line 1

原因是某个表空间删除以后，数据库还会对它进行统计，这时候就会报错了。DBA_AUTO_SEGADV_CTL表存储的是自动搜集统计信息的对象，把出错的表空间对象从这删除就可以了。先确定是哪个表空间：

SQL> desc DBA_AUTO_SEGADV_CTL
Name Null? Type
----------------------------------------- -------- ----------------------------
AUTO_TASKID NUMBER
TABLESPACE_NAME VARCHAR2(30)
SEGMENT_OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
SEGMENT_TYPE VARCHAR2(18)
PARTITION_NAME VARCHAR2(30)
STATUS VARCHAR2(40)
REASON VARCHAR2(40)
REASON_VALUE NUMBER
CREATION_TIME TIMESTAMP(6)
PROCESSED_TASKID NUMBER
END_TIME TIMESTAMP(6)

SQL> select distinct tablespace_name from DBA_AUTO_SEGADV_CTL;

TABLESPACE_NAME
------------------------------
SYSAUX
NEWHC
NEWHCINDEX
STRMADMIN

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
NEWHC
NEWHCINDEX

SQL> select count(*) from DBA_AUTO_SEGADV_CTL where tablespace_name='STRMADMIN';

COUNT(*)
----------
1

SQL> select segment_owner, segment_name, status from DBA_AUTO_SEGADV_CTL where tablespace_name='STRMADMIN';

SEGMENT_OWNER   SEGMENT_NAME    STATUS
----------------------- ---------------------- ----------------------------------------

                                                          BEING_PROCESSED
SQL> delete DBA_AUTO_SEGADV_CTL where tablespace_name='STRMADMIN';

1 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_space.auto_space_advisor_job_proc;

PL/SQL procedure successfully completed.

结果到了晚上又报错，那条记录重新加到DBA_AUTO_SEGADV_CTL中去了，猜想可能是ts$中还保留着STRMADMIN表空间的信息，把ts$中的记录也删除就完全解决了。ts$中记录了从数据库建立以来所有的创建过的表空间信息。