Questions on Scheduler

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm
Q: What is the DBMS_SCHEDULER package?
A: 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).

Q: How do you monitor a Job using the Database Control page?
A: 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.

Q: How do you add a Job using the Database Control page?
A: 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 are set to Do Not Repeat.

Q: How to create a schedule named MY_SCHEDULE owned by ISELF that executes every five seconds.
A:
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.

Q: How to schedule a job that calls your created online-backup every night at 10 p.m.?
A: 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;
/

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?
A:
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:
SQL> BEGIN
DBMS_SCHEDULER.create_job (
JOB_NAME => ‘ISELF.DAILY_GATHERING_JOB’,
PROGRAM_NAME => ‘ISLEF.DAILY_GATHERING’,
SCHEDULE_NAME => ‘MY_SCHEDULE’);
END;
/