Friday, September 15, 2006

Space advisor - ORA-20000

Ran against a bug today, with dbms_space:
ORA-12012: error on auto execute of job 8897
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

Turns out to be bug 4707226, which "will be fixed in release 11.0", explained in Note:343795.1. One of the symptoms is that you have a tablespace referenced in table DBA_AUTO_SEGADV_CTL, that no longer exists.
In my case, I used drop tablespace xxxx including contents and datafiles;
The note ends with "No workaround available". Well, here is one that worked for me:
- recreate the tablespace with a minimal sized datafile (100k will do)
- exec dbms_space.auto_space_advisor_job_proc
That should now run without problems.
- use drop tablespace xxx;
- exec dbms_space.auto_space_advisor_job_proc
Should still run without errors.
You may check DBA_AUTO_SEGADV_CTL, so verify your tablespace is not mentioned anymore.

No comments: