Tuesday, December 02, 2008

Oracle JOB cheatsheet

begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin insert into jobtest(ztime) values(systimestamp); end; '
,start_date => trunc(sysdate, 'CC')
,repeat_interval => 'FREQ=MINUTELY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;

begin
DBMS_SCHEDULER.DROP_JOB('DEMO_JOB_SCHEDULE');
end;

-- show all jobs

select *
from dba_scheduler_jobs
where owner='MHTEST';


-- job history

select log_date
, job_name
, status
from dba_scheduler_job_log
where owner='MHTEST';

-- running jobs

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs