Oracle: Getting the DDL for scheduler-jobs

Getting the DDL for tables, views etc. is easy using the package DBMS_METADATA:

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

For example, to extract the DDL for table HELP in the schema SYSTEM we use this:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'HELP', 'SYSTEM') FROM DUAL;

and we get that:

"
  CREATE TABLE "SYSTEM"."HELP" 
   (	"TOPIC" VARCHAR2(50) NOT NULL ENABLE, 
	"SEQ" NUMBER NOT NULL ENABLE, 
	"INFO" VARCHAR2(80), 
	 CONSTRAINT "HELP_TOPIC_SEQ" PRIMARY KEY ("TOPIC", "SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 49152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" "

But if we try to adapt this for to get the DDL of a scheduler-job, we’ll get stuck:

select dbms_metadata.get_ddl('JOB','JOB01') from dual;

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 4517
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8252
ORA-06512: at "SYS.DBMS_METADATA", line 2752
ORA-06512: at "SYS.DBMS_METADATA", line 2639
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31604. 00000 -  "invalid %s parameter \"%s\" for object type %s in function %s"
*Cause:    The specified parameter value is not valid for this object type.
*Action:   Correct the parameter and try the call again.

The same for scheduler-programs or -schedules:

select dbms_metadata.get_ddl('SCHEDULE','EVERY_MONDAY_800PM') from dual;

ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

The trick is to use “PROCOBJ” (stands for ‘procedural object’) as object-type for all scheduler-objects:

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', ['JOBNAME' | 'SCHEDULE' | 'PROGRAM' ]) FROM DUAL;

select dbms_metadata.get_ddl('PROCOBJ','REBUILD_INDEXES') from dual;

"  
BEGIN 
dbms_scheduler.create_job('"REBUILD_INDEXES"',
job_type=>'STORED_PROCEDURE', 
job_action=>'REBUILDINDEXES', 
number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('07-MAR-2014 12.00.00,000000000 AM CET','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), 
repeat_interval=> 'FREQ=DAILY; BYHOUR=20', 
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>FALSE, 
auto_drop=>FALSE,
comments=> 'Rebuild all indexes dependent on the Index-to-Table-Size-Ratio'
);
dbms_scheduler.enable('"REBUILD_INDEXES"');
COMMIT; 
END; 
"

Edit 2014-08-05:
Please note that it is not possible to get the DDL for scheduler-jobs owned by user SYS.

[oracle@testsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 5 13:27:34 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner, job_name, job_type from dba_scheduler_jobs where job_name='PURGE_AUDIT_TRAIL';

OWNER                          JOB_NAME                       JOB_TYPE
------------------------------ ------------------------------ ----------------
SYS                            PURGE_AUDIT_TRAIL              PLSQL_BLOCK

SQL>

SQL> SELECT DBMS_METADATA.GET_DDL('PROCOBJ','PURGE_AUDIT_TRAIL','SYS') FROM DUAL;
ERROR:
ORA-31603: object "PURGE_AUDIT_TRAIL" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

no rows selected

SQL>

As per MOS-Notes this is “because by using PROCOBJ the package [DBMS_METADATA] is using similar functionality as which datapump export would use and as known SYS objects are marked as non-exportable” (Doc ID 567504.1).
This note also states, that the lack of exporting Scheduler-DDL by using “JOB” as object-type is filed as a bug at Oracle. So there is hope, that one day we will have a more intuitive way for extracting Scheduler-DDL.

Advertisements

6 Responses to Oracle: Getting the DDL for scheduler-jobs

  1. Jon Adams says:

    Viewing the job through the Oracle Enterprise Manager console allows you to see and even make changes to SYS-owned scheduler jobs.

    • logbuffer says:

      Hi Jon,
      thanks for your input – you’re right.
      And all folks who would rather go the command-line, can also see all SYS-Jobs through the known views and edit them via the DBMS_SCHEDULER-Package I explained in another post (https://logbuffer.wordpress.com/2014/03/07/oracle-short-introduction-to-the-oracle-scheduler/), if they have the required privileges.

      What I pointed out here is the fact, that you can not get the DDL for SYS-owned jobs by using package DBMS_METADATA – what may come in handy if you have to edit or rebuild some jobs. It would save you from some tedious typing.

      As far as I know, also with OEM there is no way to get the DDL for SYS-Jobs. I would be pleased, if you can prove me wrong.

  2. fouedgray says:

    Hi and thanks for this hint, I made a test for a job owned by another user and I was connected as SYS. Seems it does not work like that , I have to be connected as that user (I don’t know the password, neither what the job itself is doing , it was a dev db i had under my fingers ) :
    SQL> select owner, job_name, job_type from dba_scheduler_jobs;

    OWNER JOB_NAME JOB_TYPE
    —————————— —————————— —————-
    ..
    ..
    EXFSYS RLM$SCHDNEGACTION PLSQL_BLOCK
    EXFSYS RLM$EVTCLEANUP PLSQL_BLOCK

    SQL> select dbms_metadata.get_ddl(‘PROCOBJ’,’RLM$SCHDNEGACTION’) from dual;

    ERROR:
    ORA-31603: object “RLM$SCHDNEGACTION” of type PROCOBJ not found in schema “SYS”

    SQL> alter session set current_schema=EXFSYS;

    Session altered.

    SQL> select dbms_metadata.get_ddl(‘PROCOBJ’,’RLM$SCHDNEGACTION’) from dual;
    ERROR:
    ORA-31603: object “RLM$SCHDNEGACTION” of type PROCOBJ not found in schema “SYS”

    and I don’t have a test db with full access to confirm that

    • logbuffer says:

      You have to explicitly name the schema owning the job like this:

      select dbms_metadata.get_ddl(‘PROCOBJ’,’RLM$SCHDNEGACTION’,’EXFSYS’) from dual;

      See the package-specification for details:

      DBMS_METADATA.GET_DDL (
      object_type IN VARCHAR2,
      name IN VARCHAR2,
      schema IN VARCHAR2 DEFAULT NULL,
      version IN VARCHAR2 DEFAULT ‘COMPATIBLE’,
      model IN VARCHAR2 DEFAULT ‘ORACLE’,
      transform IN VARCHAR2 DEFAULT ‘DDL’)
      RETURN CLOB;

  3. Alpesh Patel says:

    hi, I am trying to extract ddl statement of job class which are present for scheduled jobs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: