February 28, 2008

Oracle Scheduled Jobs

1. To change the scheduled time of oracle job and force it to run at certain time, use the set_scheduler_attribute procedure.e.g. To start job 'JOB_TEST' in next 10 min, use below script.
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
NAME => 'JOB_TEST',
ATTRIBUTE => 'start_date',
VALUE => sysdate + 10/1440
);END;


2. "RESTARTABLE" attribute specifies whether a job can be restarted in case of failure. By default, jobs are not restartable and this attribute is set to FALSE. Setting this to TRUE means that if a job fails while running, it will be restarted from the beginning point of the job.Note that setting this attribute to TRUE might lead to data inconsistencies in some situations, for example, if data is committed within a job.The Scheduler will retry the job a maximum of six times.The Scheduler will stop retrying a job when:One of the retries succeeds.All of its six retries have failed.The next retry would occur after the next regularly scheduled run of the job.You can set RESTARTABLE attribute for existing job as follows:
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE( name => 'JOB_TEST' ,
attribute => 'RESTARTABLE' ,
value => TRUE
);END;

3. If you want to run job right away, there are two wayas to do this.1. Run the job in current session.
Begin
dbms_scheduler.run_job(Job_Name);
End;

This would run the job in current session (i.e. Synchronously). Coutrol would come back to you only when the job finishes running. All the error messages (if the job fails) would be logged to the screen and you won't have to dig into the job log dictionary views( dba_scheduler_job_log, dba_scheduler_job_run_details) to find the error messages. This run will not update any columns in dictionary views. i.e. There won't be any record of this execution.

2.Run the job in backgroundIf you are running a job which takes a long time to run, and therefore you want to run the job in background and not in your current session. You can do that by setting the parameter use_current_session to false in run_job procedure. This would result in the job to be run in the background by the job threads and control would immediately return to the your session.
Begin
dbms_scheduler.run_job(Job_Name,false);
End;
This run will update all the dictionary columns to record the exeution. If the job fails then the error message won't be displayed on the screen, but you will have to query the job log dictionary views ( dba_scheduler_job_log, dba_scheduler_job_run_details) to find the error message.

1 comment:

Dmitry said...

feel free repairing corrupted files of specified format with the database repair command sql database program