SYS.DBMS_SCHEDULER .CREATE_JOB Encounters ORA-00904: ORA-06512: at "SYS.DBMS_ISCHED" ORA-06512: at "
SYS.DBMS_SCHEDULER.CREATE_JOB Encounters ORA-00904: ORA-06512: at "SYS.DBMS_ISCHED" ORA-06512: at "S
Titleimage
Posted by Patrick Hamou on 2017:09:22 14:36:52
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.