Scheduling a datapump job in Oracle 10g - Part2

Previous part of this article was about exporting a table data by using dbms_datapump via plsql. You can obtain information about creating a procedure that exports a table or group of table to an operationg system file.

This part includes information about creating a Scheduled Job to run the export procedure periodically. As Scheduled Jobs come with Oracle 10g, scripts in this article will work on databases later than Oracle 10.1.x.x

The sys package we will mainly use is DBMS_SCHEDULER. First we should create a job. You can find the following command that i used to create a job. Alternate parameters are explained with comments.

BEGIN 
    DBMS_SCHEDULER.CREATE_JOB(
       job_name           =>  'log_to_table',
       job_type           =>  'STORED_PROCEDURE',   
                              
                              -- Possible values could be:
                              -- PLSQL_BLOCK, 
                              -- STORED_PROCEDURE, 
                              -- EXECUTABLE, 
                              -- CHAIN.
       job_action         =>  'D_EPEKER.p_test_01', -- procedure name to run
       start_date         =>  'sysdate', -- date_to_start_execution date
       repeat_interval    =>  'FREQ=HOURLY', -- every other day
                              
                              -- Possible values could be:
                              -- "FREQ = YEARLY" | "MONTHLY" | "WEEKLY" | 
                              -- "DAILY" | "HOURLY" | "MINUTELY" | "SECONDLY" 
                              -- INTERVAL = 1 through 99 
         
       end_date           =>  'sysdate+1',     -- date_to_end_execution
       job_class          =>  'logging_class',  
       comments           =>  'job_logs_something');-- explanation of the job
END;

/*
Jobs are created disabled by default and need to be enabled to run. You can find the
following procedure to alter a Scheduled Jobs attribute. By default a scheduled job
drops itself after its execution ends. You should set "auto_drop" attribute to 
FALSE to hold the Scheduled Job after its execution ends.
*/
-- 
-- enabling a job 
-- 
BEGIN
  SYS.DBMS_SCHEDULER.ENABLE 
    (name => 'D_EPEKER.LOG_TO_TABLE');
END;

-- 
-- altering a job attribute
-- 
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'log_to_table',  -- name of the job
        attribute => 'auto_drop',  -- attribute name
        value => FALSE  -- new value of the attribute
        );
END;

/*
As you can schedule a job, you can also run any Scheduled Job before its Scheduled
time comes. To run or stop a job immediately you can use the following command.
*/
-- 
-- executing a job immediately 
-- 
exec DBMS_SCHEDULER.RUN_JOB('log_to_table',FALSE);
exec DBMS_SCHEDULER.STOP_JOB('log_to_table',TRUE);

/*
The most likely property of this newly "Scheduled Job" is that they can be easily
maintained and monitored by system views. You can find the related view in the
following.
*/
-- schedued jobs whichs owner is D_EPEKER
select * from dba_scheduler_jobs where owner='D_EPEKER';

-- you can also user user_scheduled_jobs view to find out the jobs and their properties
select * from user_scheduler_jobs;

-- run details of the scheduled jobs
select * from DBA_SCHEDULER_JOB_RUN_DETAILS 
  where lower(job_name) = 'log_to_file_external;

/*
There is a lot of way to determine the repeat interval. There is lots of examples 
in the following and you can also find the explanations of the frequencies.
*/
-- 
-- setting repeat_interval 
-- 
--Run every Friday
FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

--Run every other Friday
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

--Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

--Run on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-2;

--Run every 10 days
FREQ=DAILY; INTERVAL=10;

--Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

--Run on the 15th day of every other month
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

--Run on the 29th day of every month
FREQ=MONTHLY; BYMONTHDAY=29;

--Run on the second Wednesday of each month
FREQ=MONTHLY; BYDAY=2WED;

--Run on the last Friday of the year.
FREQ=YEARLY; BYDAY=-1FRI;

--Run every 50 hours.
FREQ=HOURLY; INTERVAL=50;

--Run on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

--Run hourly for the first three days of every month.
FREQ=HOURLY; BYMONTHDAY=1,2,3;

--Run on the last workday of every month, excluding company holidays 
--(This example references an existing named schedule called Company_Holidays.)
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

--Run at noon every Friday and on company holidays.
FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays