DBMS_JOBs (8i,9i)

Creating

Modifying

Deleting

Status

Job Definitions

SELECT * FROM DBA_JOBS;

Currently Running Jobs

select v$session.sid, serial#, JOB
from v$session, dba_jobs_running
where v$session.sid = dba_jobs_running.sid;

SCHEDULED_JOBs (10g+)

Creating

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name=>'QIPMU.DO_PURGE',
        job_type=>'STORED_PROCEDURE',
        job_action=>'QIPMU.SMART_VIEWS_PKG.SP_PURGETABLES',
        start_date=>trunc(sysdate+7,'D')+18/24,
        enabled=>true,
        repeat_interval => 'trunc(sysdate+7,''D'')+18/24',
        comments => 'Weekly log purge'
    );
END;
/

Modifying

BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'QIPMU.DO_PURGE',
        attribute => 'MAX_FAILURES',
        value => 3);
END;
/

Deleting

BEGIN
    DBMS_SCHEDULER.DROP_JOB('QIPMU.DO_PURGE');
END;
/

Status

select * from dba_scheduler_jobs;

select * from dba_scheduler_running_jobs;

SELECT max_failures, MAX_FAIL,
    job_priority,
    schedule_limit SCHED_LIMIT,
    logging_level
FROM user_scheduler_jobs;

select * from dba_scheduler_job_log
order by log_date desc;

select job_name, status, error#, run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS;