If you want to list all the jobs that are in the scheduler:
SELECT A.job_name
,a.last_start_date
,a.next_run_date
,a.enabled
,a.*
FROM dba_scheduler_jobs A
WHERE job_name like ('%DAILY_JOB%')
;
To find out if a job ran successfully (or not):
select a.job_name
,a.log_date
,a.status
,a.*
from dba_SCHEDULER_JOB_LOG a
WHERE a.job_name like ('%DAILY_JOB%')
order by
a.job_name
,a.log_date desc
;
If you need to see the complete DDL of the job
select dbms_metadata.get_ddl('PROCOBJ','SOME_APP_JOB','DEAN') from dual;
The parameters are:
PROCOBJ = procedural object which cover all objects in the scheduler SOME_APP_JOB = Name of the job DEAN = Schema that contains the job
It does not seem to be possible to get the DDL for a job in the SYS schema. For example:
select dbms_metadata.get_ddl('PROCOBJ','FILE_WATCHER','SYS') from dual;
Will result in:
ORA-31603: object "FILE_WATCHER" of type PROCOBJ not found in schema "SYS" ORA-06512: at "SYS.DBMS_METADATA", line 6069 ORA-06512: at "SYS.DBMS_METADATA", line 8666 ORA-06512: at line 1 31603. 00000 - "object \"%s\" of type %s not found in schema \"%s\"" *Cause: The specified object was not found in the database. *Action: Correct the object specification and try the call again.
One way to get around this is to copy the job to another schema as follows:
exec dbms_scheduler.copy_job('SYS.FILE_WATCHER',’DEAN.FILE_WATCHER’);
The parameters in the above SQL are ‘old_schema.job_name’ to ‘new_schema.jobname’.
As an FYI, a copied job will be created in (i.e. copied into) the new schema as a DISABLED job.
After you get the DDL, you can drop the job with:
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => '"SYS"."FILE_WATCHER"',
defer => false,
force => true);
END;
As mentioned above, a copied job will be created in (i.e. copied into) the new schema as a DISABLED job. You can enable the job with:
exec dbms_scheduler.enable('DEAN.SOME_APP_JOB');