Oracle's dbms_metadata.get_ddl for object_type JOB
I'd like to create ddl scripts for most of my database objects. dbms_metadata.get_ddl works for most of the object types. For instance the following creates the ddl for a view:
select dbms_metadata.get_ddl ( 'VIEW', 'SAMPLE_VIEW') from dual
On the other hand it's not working for object_type 'JOB'. The following:
select dbms_metadata.get_ddl( 'JOB', 'SAMPLE_JOB' ) from dual
gives the following 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 4705
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8582
ORA-06512: at "SYS.DBMS_METADATA", line 2882
ORA-06512: at "SYS.DBMS_METADATA", line 2748
ORA-0开发者_JAVA技巧6512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1
If I list my jobs using
select * from user_objects where object_type='JOB'
it shows SAMPLE_JOB (just like it shows SAMPLE_VIEW if filtered for object_type='VIEW').
Why is it working for VIEW (and TABLE, INDEX, TRIGGER, ...) and not for JOB?
I'm using Oracle 10g.
select dbms_metadata.get_ddl('PROCOBJ', 'yourJobNameGoesHere') from dual;
PROCOBJ's are procedural objects.
select dbms_metadata.get_ddl('PROCOBJ',['JOB'|'PROGRAM'|'SCHEDULE'],'OWNER') from dual;
The PROCOBJ
can be JOB, PROGRAM and SCHEDULE.
Alternative, get all jobs from the database with their DDL:
select owner, job_name, dbms_metadata.get_ddl('PROCOBJ', job_name, owner) as ddl_output from ALL_SCHEDULER_JOBS
Even I tried all above to get DDL in Oracle version 10g, but no success.
Here is what I figure out to get the detail of the job:
set pages 200 lines 200
col owner format a20
col job_name format a25
col JOB_ACTION format a75
col COMMENTS format a60
select owner, job_name, next_run_date, state, enabled from dba_scheduler_jobs where job_name like '%AUDIT%';
-- get the detail of scheduled jobs.
select OWNER,JOB_NAME, JOB_ACTION, COMMENTS FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- get the limited detail from the selected column.
select * FROM DBA_SCHEDULER_JOBS where JOB_NAME='PURGE_AUDIT_LOG';
-- to get the complete detail of a specific job along with code and other details.
精彩评论