Category: Useful SQL

Some useful scheduler related commands

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');

Simulating an Oracle error into the alert log

In an Oracle AWS RDS instance we do not have direct access to the server and hence we could not use our regular CRON scripts. I was testing an Oracle Stored procedure to replace this functionality and needed to simulate an error in the alert log. I found the below web site

Simulating ORA-errors

(nice site with a good explanation)

and used these commands:

alter session set events '942 incident(SIMULATED_ERROR)';
drop table tablethatdoesnotexist;
alter session set events '942 trace name context off';

The alert log contained:

ORA-00700: soft internal error, arguments: [EVENT_CREATED_INCIDENT], [942], [SIMULATED_ERROR], [], [], [], [], [], [], [], [], []
ORA-00942: table or view does not exist

This was exactly what I needed for my testing.