is you query dba_objects for your job objects, the objecttype is "JOB". However, if you use this type to extract your DDL definition, you get the following:
SQL> select dbms_metadata.get_ddl('JOB','COORD_INC_BACKUP','DBA_ADMIN') from dual;
ERROR:
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function
SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4676
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8552
ORA-06512: at "SYS.DBMS_METADATA", line 2881
ORA-06512: at "SYS.DBMS_METADATA", line 2747
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
no rows selected
Infact, the object type of your job object is not JOB, but PROCJOB
SQL> set long 2000000
SQL> set pagesize 0
SQL> select dbms_metadata.get_ddl('PROCOBJ','COORD_INC_BACKUP','DBA_ADMIN') from dual;
BEGIN
dbms_scheduler.create_job('"COORD_INC_BACKUP"',
job_type=>'EXECUTABLE', job_action=>
'/u01/app/oracle/scripts/rman_coord_inc.sh'
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
NULL
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'coordinated dump (incremental 1) using rman'
);
COMMIT;
END;
SQL>
3 comments:
I GOT ERROR WHEN EXECUTING FOLLOWING SQL STATEMENT TO GET SCHEDULER JOB DDL
SQL> select dbms_metadata.get_ddl('PROCOBJ','DAILY_BACKUP_JOB','SYS') from dual;
ERROR:
ORA-31603: object "DAILY_BACKUP_JOB" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2806
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
PLEASE SUGGEST ME
THANKS
DILIP BAKSHI
Check for the owner of the Job.
And include the name of owner likewise in the Select command.
SQL>select dbms_metadata.get_ddl('PROCOBJ','DAILY_BACKUP_JOB','') from dual;
How to get for dbms_jobs this is only working for dba_scheduler_jobs ? Any Idea? Thanks much
Post a Comment