Oracle Scheduler Jobs

First we will be creating the program, and then the scheduler afterwards a job:

SQL> begin
  2  dbms_scheduler.create_program(
  3  program_name=>'test',
  4  program_type=>'PLSQL_BLOCK',
  5  program_action=>'begin dbms_stats.gather_schema_stats(''scott''); end;',
  6  enabled=>TRUE,
  7  comments=>'program to gather scott''s statistics using a pl/sql block.');
  8* end;


PL/SQL procedure successfully completed.

SQL> SELECT owner, program_name, enabled FROM dba_scheduler_programs;

OWNER                          PROGRAM_NAME                   ENABLED
----------------                -----------------------               --------------
SYS                                 TEST                                          TRUE


Creating Hourly Scheduler 

SQL> begin
  2  dbms_scheduler.create_schedule(
  3  schedule_name=>'test_hourly',
  4  start_date=>systimestamp,
  5  repeat_interval=>'freq=hourly; byminute=0',
  6  end_date=>NULL,
  7  comments=>'repeats forever');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Creating Scheduler that runs every minute.

SQL> begin
  2  dbms_scheduler.create_schedule(
  3  schedule_name=>'test_min',
  4  start_date=>systimestamp,
  5  repeat_interval=>'freq=minutely,interval=5',
  6  end_date=>NULL,
  7  comments=>'Repeat every 5 mins');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Now, Creating a job with the name Test_job and associating the program which is already created and scheduling it according to the schedule(Minutely or hourly)

SQL> begin
  2  dbms_scheduler.create_job(
  3  job_name=>'TEST_JOB',
  4  program_name=>'TEST',
  5  schedule_name=>'test_min',
  6  enabled=>TRUE,
  7  comments=>'job defined by an existing program and schedule.');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select owner,job_name,enabled from dba_scheduler_jobs where Job_name='TEST_JOB'

OWNER         JOB_NAME          ENABLED
--------------------------------------------------
SYS                TEST_JOB              TRUE




This query will show the job name and the last or recently executed date with timestamp

SQL> select job_name,last_start_date from dba_scheduler_jobs where job_name='TEST_JOB';

JOB_NAME        LAST_START_DATE
---------------      --------------------------------------------
TEST_JOB          24-JUL-11 08.16.53.477586 AM +05:30



  1* select LOG_ID,JOB_NAME,STATUS from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_JOB'
SQL> /

    LOG_ID      JOB_NAME   STATUS
    ----------    ---------------  ----------
      131            TEST_JOB      SUCCEEDED

      142            TEST_JOB      SUCCEEDED

      143            TEST_JOB      SUCCEEDED