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:

1-  create the temporary user
create user vfenoll identified by tempuser1234;

2-  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’;

3-  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’);
[…]

4-  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

5-  extract the DDL for scheduler jobs
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.

 

6-  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