|
‘The Seven
Deadly Sins. Wealth
without work, pleasure without conscience, knowledge without
character, commerce without morality, science without humanity,
worship without sacrifice, and politics without principle.’
Gandhi |
Scheduler in the Oracle 10g
database
Introduction
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, 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-Creating Job
using the EM Database Control
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 the 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-Creating Job to
be executed periodically
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-Creating Job by
using executable program
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-Creating Job by
using program
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;
/
|
“It may be true
that the law cannot make a man love me, but it can stop him from
lynching me, and I think that's pretty important.” Martin
Luther King Jr. |
Questions:
Questions on
Scheduler in
the Oracle 10g database
Q: What is the
DBMS_SCHEDULER package?
Q: How do you monitor a Job
using the Database Control page?
Q: How do you add a Job
using the Database Control page?
Q: How to create a schedule
named MY_SCHEDULE owned by ISELF that executes every five seconds.
Q: How to schedule a job
that calls your created online-backup every night at 10 p.m.?
Q: Assuming that you have a
procedure that collects information daily called DAILY_DATA_GATHERING.
Now, you should create a problem to call this procedure and create a
job to run it daily. How you do that?
|