joi, 18 aprilie 2013

Keep it simple! Schedule a job in no time!

Very often, the Oracle DBAs are in the middle of tons of tasks, running out of time, craving for speedy answers for their problems. One of the big time eaters issues related to db admining is the confusing syntax of scheduling a job. Shortly, use the following plsql code and leave the theory for those who are entitled to do it.
First, let's create a table just for test purposes, keep in  mind you don't  necessarily  have to do it.

create table table_of_moments (moment date);


/* note that the above code (the 3 commented lines) has to be uncommented when you will, eventually, run for the second or more than second time*/

  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_schedule',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',

/*well, here let's discuss just a bit about the posibilities, i am sure the needs of scheduling various times and intervals are really infinite:
START_DATE could be, of course, SYSDATE or SYSDATE+1 for a day past, or +1/24 for an hour past, or +1/24/120 for 30 seconds past, choose what it fits your needs.
repeat_interval could be:
FREQ: DAILY, HOURLY, MINUTELY, SECONDELY, i don't know if can be anything else
INTERVAL: this parameter is pretty straight forward, you can imagine by yourselves how can you use it
The syntax for these two fields is the key of scheduling and is enormously rich but, remember, the actual scope here is to schedule a job quick and move on, to another tasks

    end_date        => NULL,
    comments        => 'This schedule test will trigger every 30 seconds from NOW .');

DBMS_SCHEDULER.create_program (
    program_name   => 'test_program',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN 
                                   insert into table_of_moments values (sysdate)
                           commit; END;',
    enabled        => TRUE,
    comments       => 'This program will fill the test table with the exact value of moment time the program was triggered .');

(Job_name => 'test_job',
Program_name => 'test_program',
Schedule_name => 'test_schedule',
Enabled => TRUE,
Comments => 'This job will make the things working .');


Now, let's check the things are working:

select * from table_of_moments; --repeat this command how many times you                                          

These being said, i remind you that the ways of scheduling a job are so various than you can study days in order to encompass them entirely. You can use dbms_scheduler or dbms_post and you can call many methods to submit or schedule a job. The present tip from this post is just about speed of doing right things.

Niciun comentariu:

Trimiteți un comentariu