iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

Scheduler in the Oracle 10g database

 

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

 

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;

        /

 

 

 

Google
 
Web web site