Schedule Jobs of Oracle Database

Jobs for Oracle 10g

Create a job.

DBMS_JOB:

-- run
begin
  dbms_job.run(9);  /* job id */
end;
/

-- disable
begin
  dbms_job.broken(9,true);
end;
/

-- enable
begin
  dbms_job.broken(9,false);
end;
/

-- remove
begin
  dbms_job.remove(9);
end;
/

Schedule Jobs for Oracle 11g

-- Create

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_INSERT_TBL01',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN p_random_tbl01; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY; INTERVAL=10',
        enabled         => TRUE
    );
END;
/

-- Query

SELECT job_name, state, enabled, next_run_date, comments
  FROM user_scheduler_jobs
  WHERE job_name = 'JOB_INSERT_TBL01';

-- Drop

BEGIN
    DBMS_SCHEDULER.DROP_JOB('JOVE.JOB_INSERT_TBL01', TRUE);
END;
/

-- Run
BEGIN
    DBMS_SCHEDULER.RUN_JOB('JOVE.JOB_INSERT_TBL01');
END;
/