Custom Search . . .

Monday, October 22, 2007

ORA-12012: error on auto execute of job

Version: 10.2.0.2.0
OS : Windows

I am facing auto execute job failure in my developer's database. Metalink suggest it's bug in oracle 10.2.0.2.0. This bug fixied in 10.2.0.3.0.

References:
343795.1

But manually you can able to solve this issue.


SQL> connect sys@devdb as sysdba
Enter password:
Connected.

SQL> exec dbms_space.auto_space_advisor_job_proc

Errors in file d:\oracle\admin\devdb\bdump\devdb_j001_329716.trc:
ORA-12012: error on auto execute of job 91056
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

SQL> SELECT DISTINCT(TABLESPACE_NAME) FROM DBA_AUTO_SEGADV_CTL;

TABLESPACE_NAME
------------------------------
SAMIDCR
SAMIHRD
SYSAUX
SAMIFINANCE
SAMIPRODUCTION
FORAUDIT
TRADINGUSA
EXAMPLE
SAMIINVENTORY
SAMIPURCHASE
SAMISALES

11 rows selected.

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ( SELECT TABLESPACE_NAME FROM DBA_AUTO_SEGADV_CTL);

TABLESPACE_NAME
------------------------------
SAMIDCR
SAMIHRD
SYSAUX
SAMIFINANCE
SAMIPRODUCTION
FORAUDIT
TRADINGUSA
SAMIINVENTORY
SAMIPURCHASE
SAMISALES

10 rows selected.

Here "Example" - Tablespace not available. Manually create this tablespace..

SQL> CREATE TABLESPACE EXAMPLE DATAFILE 'D:\ORACLE\ORADATA\DEVDB\EXAMPLE01.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 50M;

Tablespace created.

SQL> exec dbms_space.auto_space_advisor_job_proc

PL/SQL procedure successfully completed.


Or.

1. Connect sys as sysdba
2. Execute “prvtspcu.plb” and “prvtadv.plb” from your oracle home directory.
3. exec dbms_space.auto_space_advisor_job_proc

4 comments:

LSHARMA said...

Hi Babu,
Really good one.
Thanks for your efforts for making these documents.

Babu said...

Thanks for your feedback

Anonymous said...

A BIG THANKS

Sam said...

Thanks , it has been used in production environment