Sunday, July 24, 2011

create job using new 10g scheduling feature


Oracle 10g intorduce a comprehensive scheduler (DBMS_SCHEDULER) to extend the functionality provided by the DBMS_JOB package. Jobs can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them. In order to crate job,schedule or program owner require CREATE JOB privilege to be granted.

Programs:
-----------------
The scheduler allows you to optionally create programs which hold metadata about a task, but no schedule information. A program may related to a PL/SQL block, a stored procedure or an OS executable file. Programs are created using the CREATE_PROGRAM procedure:

-- Create the test program.

BEGIN
-- PL/SQL Block.
DBMS_SCHEDULER.create_program (
program_name => 'test_plsql_block_program',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_STATS.gather_schema_stats(''KUNAL''); END;',
enabled => TRUE,
comments => 'Program to gather JOHN''s statistics using a PL/SQL block.');

DBMS_SCHEDULER.enable (name => 'test_plsql_block_program');
END;
/

-- Display the program details.

SELECT program_name,program_type,enabled FROM user_scheduler_programs;

PROGRAM_NAME PROGRAM_TYPE ENABLE
------------------------------ ---------------- -----
TEST_PLSQL_BLOCK_PROGRAM PLSQL_BLOCK TRUE

Programs can be deleted using the DROP_PROGRAM procedure:

BEGIN
DBMS_SCHEDULER.drop_program (program_name => 'test_plsql_block_program');
END;
/

Schedules:
------------------

The scheduler allows you to optionally create Schedules. Schedules define the start time, end time and interval related to a job. Schedules are created using the CREATE_SCHEDULE procedure:

-- Create the schedule

BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name => 'test_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
comments => 'Repeats hourly, on the hour, for ever.');
END;
/

-- Display the schedule details.

SELECT schedule_name,start_date FROM user_scheduler_schedules;

SCHEDULE_NAME START_DATE
-------------------------- --------------------------
TEST_SCHEDULE 27-APR-04 11.32.33.604343 AM -05:00


A schedule can be dropped using the DROP_SCHEDULE procedure:

BEGIN
DBMS_SCHEDULER.drop_schedule (schedule_name => 'TEST_SCHEDULE');
END;
/

PL/SQL procedure successfully completed.


Jobs:
-------
Jobs are the main part of the scheduler. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them:

-- Create jobs.

-- Job defined entirely by the CREATE JOB procedure.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_self_contained_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''JOHN''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job created using the CREATE JOB procedure.');
End;
/

-- Job defined by an existing program and schedule.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_program_schedule_job',
program_name => 'test_plsql_block_program',
schedule_name => 'TEST_SCHEDULE',
enabled => TRUE,
comments => 'Job created using an existing program and schedule.');
End;
/

-- Display job details.

SELECT job_name, enabled FROM user_scheduler_jobs;

JOB_NAME ENABL
------------------------------ -----
TEST_PROGRAM_SCHEDULE_JOB TRUE
TEST_SELF_CONTAINED_JOB TRUE

Jobs are normally run under the control of the job coordinator, but they can be controlled manually using the RUN_JOB and STOP_JOB procedures:

BEGIN
DBMS_SCHEDULER.run_job (job_name => 'TEST_PROGRAM_SCHEDULE_JOB',
use_current_session => FALSE);
END;
/

BEGIN
DBMS_SCHEDULER.stop_job (job_name => 'TEST_PROGRAM_SCHEDULE_JOB');
END;
/

Jobs can be deleted using the DROP_JOB procedure:

BEGIN
DBMS_SCHEDULER.drop_job (job_name => 'TEST_PROGRAM_SCHEDULE_JOB');
DBMS_SCHEDULER.drop_job (job_name => 'test_self_contained_job');
END;
/


Ref: oracle-base.com

0 comments:

Post a Comment

Auto Scroll Stop Scroll