SYS.DBMS_SCHEDULER.CREATE_JOB Encounters ORA-00904: ORA-06512: at "SYS.DBMS_ISCHED" ORA-06512: at "SYS.DBMS_SCHEDULER"

SYS.DBMS_SCHEDULER.CREATE_JOB Encounters ORA-00904: ORA-06512: at "SYS.DBMS_ISCHED" ORA-06512: at "S

APPLIES TO: Oracle Database - Enterprise Edition

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
*** Checked for relevance 25-Sept-2014 ***
*** Checked for relevance 22-Mar-2016 ***

SYMPTOMS: ORA-6512; ORA-904

Error when attempting to create a job via DBMS_SCHEDULER:

SYS.DBMS_SCHEDULER.CREATE_JOB
(job_name => 'EDI.ACH_JOB',schedule_name => 'EDI.SUN0200',
program_name => 'EDI.ACH_PROG',
comments => NULL);

BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB
(job_name => 'EDI.ACH_JOB',
schedule_name => 'EDI.SUN0200',
program

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 319
ORA-06512: at line 1


Not able to run the ultrp.sql command without encountering an error.
Results of dba_registry showed that many components (especially CATPROC) were invalid.

CAUSE: data dictionary, scheduler objects

Issues with the data dictionary and scheduler objects.

SOLUTION

Confirm you have a current and working backup of the database, then:

​
startup upgrade as sysdba:

1. $ORACLE_HOME/rdbms/admin/catnomwn.sql
2. $ORACLE_HOME/rdbms/admin/catnosch.sql
3. $ORACLE_HOME/rdbms/admin/catsch.sql
4. $ORACLE_HOME/rdbms/admin/catmwin.sql
5. $ORACLE_HOME/rdbms/admin/utlrp.sql

​

If the CATALOG or CATPROC components still remain Invalid in dba_registry, then you may have to recreate the data dictionary.

$ORACLE_HOME/rdbms/admin/catalog.sql
$ORACLE_HOME/rdbms/admin/catproc.sql

 

NOTE:  Make sure you backup your job definitions prior to performing the above mentioned action plan. You will need to add all jobs back into the scheduler as this will remove them.

Return to Blog