|
|
Scheduler in the Oracle 10g database
Scheduler
in the Oracle 10g database Oracle
Database 10g provides scheduling capabilities through the database Scheduler. It
uses the DBMS_SCHEDULER package. The Scheduler offers far more functionality
than the DBMS_JOB package. You can create a job class a job class always belongs
to the SYS schema. Since the priority of jobs change over a period of time, now
you can also create a window. For example: you can create a window for the month
of October that uses the END_OF_YEAR plan and is active every day from 8:00 a.m.
to 6:00 p.m. Eastern standard Time (EST). Hands-ON
#1: How
to monitor the scheduler? Connect
as sysdba and grant the DBA system privilege to the ISELF user. SQL>
CONNECT / AS SYSDBA SQL>
GRANT DBA TO ISELF; How
to monitor a Job using the Database Control page: Log
in to EM Database Control as the ISELF user. From the Database Control home page
click on the Administration tab. In the ‘Scheduler’ region, click the
‘Jobs’ link. You
should see: One
schedule, called DAILY_PURGE_SCHEDULE by clicking the Schedulers link, Two
windows, called WEEKNIGHT_WINDOW and WEEKEND_WINDOW by clicking the Windows
link, and Two
job classes, called DEFUALT_JOB_CLASS, AUTO_TASKS_JOB_CLASS by clicking the Job
Classes link. How
to add a Job using the Database Control page: Click
the Create button on the Scheduler Jobs page, Fill
out the applicable fields, Back
to the Create Job page, enter location of your job script in the Executable Name
field, and then click the Schedule tab. On
the Schedule page, make sure that the immediate radio button and the Repeat
field is set to Do Not Repeat. Hands-On
#2: Create
a schedule named MY_SCHEDULE owned by ISELF that executes every five seconds. SQL>
CONNECT iself/schooling SQL>
BEGIN DBMS_SCHEDULER.create_schedule
( SCHEDULE_NAME
=>
‘MY_SCHEDULE’, START_DATE
=> SYSTIMESTAMP, REPEAT_INTERVAL
=>
‘FREQ=SECONDLY;INERVAL=5’, COMMENTS
=> ‘This
is my first created schedule.’);
END;
/ Now,
you should be able to see it in your Database Control page. Hands-ON
#3: Create
a job that calls your created online-backup every night at 10 p.m. You should
have been granted CREATE JOB to be able to create a job. Jobs are created as
disabled by default. You must enable them explicitly. SQL>
BEGIN DBMS_SCHEDULER.create_job
( JOB_NAME
=> ‘ISELF.ONLINE_BACKUP’, JOB_TYPE
=> ‘EXECUTABLE’, JOB_ACTION
=> ‘/home/my_Nightly_online_backup.sh’, START_DATE
=> TRUNC(SYSDATE+1)+22/24, REPEAT_INTERVAL
=>
‘TRUNC(SYSDATE+1)+22/24’, COMMENTS
=> ‘My
nightly online backup’);
END;
/ Notice
that the JOB_TYPE can be PLSQL_BLOCK, STORED_PROCEDURE, and EXECUTABLE. The
REPEAT_INTERVAL can be HOURLY, DAILY, MINUTELY, YEARLY or BYMONTH, BYWEEKNO,
BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE, BYSECOUND. For example, FREQ=BYWEEKNO=4,7,52
or BYDAY=MON, etc. SQL>
EXEC DBMS_SCHEDULER.enable(‘ISELF.ONLINE_BACKUP’); Hands-On
#4: Assuming
that you have a procedure that collects information daily called
DAILY_DATA_GATHERING. You can create a problem to call this procedure and create
a job to run it daily. SQL>
BEGIN DBMS_SCHEDULER.create_program
( PROGRAM_NAME
=>
‘DAILY_GATHERING’, PROGRAM_ACTION
=>
‘ISLEF.DAILY_DATA_GATHERING’, PROGRAM_TYPE
=>
‘STORED_PROCEDURE’, ENABLED
=> TRUE);
END; SQL>
BEGIN DBMS_SCHEDULER.create_job
( JOB_NAME
=> ‘ISELF.DAILY_GATHERING_JOB’, PROGRAM_NAME
=>
‘ISLEF.DAILY_GATHERING’, START_DATE
=> TRUNC(SYSDATE+1)+22/24, REPEAT_INTERVAL
=>
‘TRUNC(SYSDATE+1)+22/24’, COMMENTS
=> ‘Daily
Data Gathering Job.’);
END;
/ You
could also use your created schedule on hands-on #2: SQL>
BEGIN DBMS_SCHEDULER.create_job
( JOB_NAME
=> ‘ISELF.DAILY_GATHERING_JOB’, PROGRAM_NAME
=>
‘ISLEF.DAILY_GATHERING’, SCHEDULE_NAME
=>
‘MY_SCHEDULE’);
END;
/
|
|