Blog

Do you know how to get DDL of a dbms_scheduler job under sys schema in Oracle, if you are looking for the same then this article is for you. In our previous article, we have already seen how to find the ddl of the table, tablespaces, user etc. but in similar way, you cannot get ddl of dbms_scheduler job information. But there is an alternative method. Today we will discuss on that. So lets go though the steps and at the end you will be able to get that :

1. First see the job,owner name and the status :

select owner,job_name,state from dba_scheduler_jobs where job_name='PURGE_LOG';

2. Here we can see that owner is sys schema and also we see that it is throwing error if we want to get it in same way.

3. So to resolve that, we have to copy the job to any other schema.

exec dbms_scheduler.copy_job('SYS.PURGE_LOG','HR.PURGE_LOG');

4. Now you can check the job name and owner again from the below query :

select owner,job_name,state from dba_scheduler_jobs where job_name='PURGE_LOG';

5. Now try to find the DDL in the same way. You will be able to find it.

set long 99999
set lines 300
set pages 400
select dbms_metadata.get_ddl('PROCOBJ','PURGE_LOG','HR') from dual;

So, here are some the steps that you can see how to find DDL of a dbms_scheduler job under sys schema. Hope this Helps!!

Also you can get reference from the Oracle Doc as well.