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