|
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 ORACLE user.
SQL> CONNECT / AS SYSDBA
SQL> GRANT DBA TO ORACLE;
How to monitor a Job using the Database Control
page:
Log in to EM Database Control as the ORACLE 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 ORACLE
that executes every five seconds.
SQL> CONNECT oracle/learning
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 => �ORACLE.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(�ORACLE.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 =>
�ORACLE.DAILY_DATA_GATHERING�,
PROGRAM_TYPE => �STORED_PROCEDURE�,
ENABLED =>
TRUE);
END;
SQL> BEGIN
DBMS_SCHEDULER.create_job (
JOB_NAME => �ORACLE.DAILY_GATHERING_JOB�,
PROGRAM_NAME => �ORACLE.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 => �ORACLE.DAILY_GATHERING_JOB�,
PROGRAM_NAME =>
�ORACLE.DAILY_GATHERING�,
SCHEDULE_NAME => �MY_SCHEDULE�);
END;
/
|
