DBMS_SCHEDULER

When you back in the day wanted to schedule or run something in the backgound you used DBMS_JOB. With  the beginning of release 10g of the database new functionality was introduced to accomplish this, the DBMS_SCHEDULER.

DBMS_SCHEDULER is so much more flexible the DBMS_JOB and makes everything easier, some examples:

• To set up your intervals you just specify the parameter repeat_interval.
  MON-FRI 22.00: repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=22;'
  Every hour, every day: repeat_interval => 'FREQ=HOURLY;INTERVAL=1'
  Every monday, 05:00: repeat_interval=> 'FREQ=DAILY; BYDAY=MON; BYHOUR=5;'
• You will not get the problem with shifting start times that you have with DBMS_JOB.
• Possible to create chains of jobs that are dependent of each other.
• Possible to schedule:
   - Execution of host scripts.
   - E-mail notifications.
   - Execute host scripts on remote servers (requires an agent on the remote server).
   - Execute jobs on multiple databases.
• You can create a file watcher that will let you monitor a folder for files. When new files arrives the specified job will start.

To be able to use all the functions of DBMS_SCHEDULER you need to be on 11g release 2. There is a lot of artictles about the DBMS_SCHEDULER but you can get a good overview with examples can be found here.

One of the cooler features with the DBMS_SCHEDULER is the file watcher. I will illustrate the functionality with an exampel.

Create a new user and directory

CREATE USER fw_test_usr IDENTIFIED BY test;
GRANT connect, resource, dba TO fw_test_usr;

CREATE OR REPLACE DIRECTORY demo_dir AS '/home/oracle/tmp';
GRANT read, write ON DIRECTORY demo_dir TO fw_test_usr;

CREATE OR REPLACE DIRECTORY demo_old_dir AS '/home/oracle/tmp/old';
GRANT read, write ON DIRECTORY demo_old_dir TO fw_test_usr;

The file watchers will by default check for files every 10 minutes, we will decrease that to every minute.

CONN / AS SYSDBA

BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/

Now, logged in as fw_test_usr we need to set up OS privilege for file access.

BEGIN
  DBMS_SCHEDULER.create_credential(
    credential_name => 'watcher_credential',
    username        => 'oracle',
    password        => 'oracle');
END;

SELECT * FROM dba_scheduler_credentials;

The next step is to create the file watcher. The file watcher is not enabled yet (enabled => FALSE).

BEGIN
  DBMS_SCHEDULER.create_file_watcher(
    file_watcher_name => 'demo_file_watcher',
    directory_path    => '/home/oracle/tmp',
    file_name         => '*.txt',
    credential_name   => 'watcher_credential',
    destination       => NULL,
    enabled           => FALSE);
END;

SELECT * FROM dba_scheduler_file_watchers;

Create a table and the procedure that will be used by the file watcher.

CREATE TABLE fw_demo_tbl_output 
 (message VARCHAR2(4000),
  row1    VARCHAR2(4000));

CREATE OR REPLACE
PROCEDURE fw_demo_proc (p_fileinfo SYS.SCHEDULER_FILEWATCHER_RESULT) AS

  v_file    utl_file.file_type;
  v_message VARCHAR2(4000);
  v_row1    VARCHAR2(4000);
BEGIN
  -- file path, name and size
  v_message := p_fileinfo.directory_path||'/'||
               p_fileinfo.actual_file_name||'('||
               p_fileinfo.file_size||')';

  -- read the first row of the file
  v_file := utl_file.fopen('DEMO_DIR', p_fileinfo.actual_file_name, 'R');
  LOOP
    BEGIN
      utl_file.get_line(v_file, v_row1);
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
  utl_file.fclose(v_file);

  -- move the file to the old directory with a new name
  utl_file.frename
     (src_location  => 'DEMO_DIR',
      src_filename  => p_fileinfo.actual_file_name,
      dest_location => 'DEMO_OLD_DIR',
      dest_filename => p_fileinfo.actual_file_name||'.'||to_char(SYSDATE, 'YYMMDDHH24MISS'),
      overwrite     => FALSE);

  INSERT INTO fw_demo_tbl_output (message, row1) VALUES (v_message, v_row1);
  COMMIT;
END;

Create a Program object with a Metadata argument (what will happen when the file watcher event is raised). The program must reference the event_message to retrieve information about the file, such as it's name. The program is not enabled yet (enabled => FALSE).

BEGIN
  DBMS_SCHEDULER.create_program(
    program_name        => 'fw_demo_prog',
    program_type        => 'stored_procedure',
    program_action      => 'fw_demo_proc',
    number_of_arguments => 1,
    enabled             => FALSE);
END;

BEGIN
  DBMS_SCHEDULER.define_metadata_argument(
    program_name       => 'fw_demo_prog',
    metadata_attribute => 'EVENT_MESSAGE',
    argument_position  => 1);
END;

SELECT *
FROM dba_scheduler_programs
WHERE program_name= 'FW_DEMO_PROG'
ORDER BY 1;

SELECT *
FROM dba_scheduler_program_args
WHERE program_name = 'FW_DEMO_PROG';

Now, create the job. The job is not enabled yet (enabled => FALSE).

BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'fw_demo_job',
    program_name    => 'fw_demo_prog',
    event_condition => NULL,
    queue_spec      => 'demo_file_watcher',
    auto_drop       => FALSE,
    enabled         => FALSE);
END;

SELECT *
FROM dba_scheduler_jobs
WHERE job_name='FW_DEMO_JOB';

By default, the arrival of new files will be ignored if the job is already running. If you need the job to fire for each new arrival, regardless of whether the job is already running or not, set the PARALLEL_INSTANCES attribute for the job to true. The job will then be run as a lightweight job.

BEGIN
  DBMS_SCHEDULER.set_attribute('fw_demo_job','parallel_instances',TRUE);
END;

The last step is to enable everything.

BEGIN
  DBMS_SCHEDULER.enable('demo_file_watcher');
  DBMS_SCHEDULER.enable('fw_demo_prog');
  DBMS_SCHEDULER.enable('fw_demo_job');
END;

Now create some .txt files in folder /home/oracle/tmp on the server. Then wait a couple a minutes and the information sould appear in the table. The files should also have been moved to the old folder on the server.

SELECT * FROM  fw_demo_tbl_output;