How to extract DDL for scheduler jobs owned by SYS?

We can’t export scheduler jobs which are owned by SYS user, even in Oracle 18c and 12c. If you try to get DDL for scheduler jobs owned by SYS you’ll hit ORA-31603 error
The solution of this problem is to copy the scheduler job to another user (e.g. vfenoll) and then extract the DDL with dbms_metadata.

 

Here is the steps with an example:

— create the temporary user
create user vfenoll identified by tempuser1234;

— build the copy statement
set head off lines 130
select ‘exec dbms_scheduler.copy_job(”SYS.’ || job_name || ”’, ”VFENOLL.’ || job_name || ”’);’ from dba_scheduler_jobs where owner=’SYS’;

— select the jobs you want to copy (probably at the end of the list) and execute the copy_job for your selection
[…]
exec dbms_scheduler.copy_job(‘SYS.PURGE_FGA_TRAILS’, ‘VFENOLL.PURGE_FGA_TRAILS’);
[…]

# Build the DDL statements
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER OFF

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

spool gen_sys_scheduler_jobs.sql
select ‘select dbms_metadata.get_ddl(”PROCOBJ”,”’ || job_name || ”’,”VFENOLL”) from dual;’ from dba_scheduler_jobs where owner=’VFENOLL’;
spool off

# extract the DDL
set long 2000 head off
spool sys_scheduler_jobs.sql
@gen_sys_scheduler_jobs.sql
spool off
[…]

BEGIN
dbms_scheduler.create_job(‘”PURGE_FGA_TRAILS”‘,
job_type=>’PLSQL_BLOCK’, job_action=>
‘BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(2, TRUE); END;’
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
‘FREQ=HOURLY;INTERVAL=24’
, end_date=>NULL,
job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE,comments=>
‘Audit clean job = ”PURGE_FGA_TRAILS”’
);

COMMIT;
END;
/

[…]
spool off

 

— Format the output, change enabled=>FALSE   to TRUE, remove the COMMITs.

You can also recreate the jobs in Cloud Control with the all the informations you gathered.

 

— drop the temporary user with the jobs
drop user vfenoll cascade;

 

It’s never a good idea to create jobs (or other objects) in SYS, prefer another schema :)

Here is another post to extract the DDL of a user with dbms_metadata.get_ddl user.  Get the script for the user creation also for the roles & object grant & system grants provided to this user.

 

Author: V. Fenoll Oracle DBA Montreal

Compatibility: Oracle 18c, 12c, 11g, 10g

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.