Home  >  Oracle Database Administration (DBA)  >  Maintenance  >  Managing Oracle Database Jobs

What a database job is?

All the time we need some PL/SQL scripts to run at a specific period of time regularly. This is done by scheduling the database jobs to run following some rules. A job define what we have to run, when and at which interval. When a job is created (submitted), the job is put in a queue. The queue is monitored by a coordinator job queue (CJQ0) background process. The coordinator (CJQ0) periodically selects jobs that are ready to run from the jobs shown in the DBA_JOBS view. The time interval the coordinator looks for new jobs to start is establish by JOB_QUEUE_INTERVAL initialization parameter. JOB_QUEUE_INTERVAL set to 60 means that the CJQ0 will look at every minutes to see if is any job scheduled to run at this time. When CJQ0 find a job which must start, CJQ0 spawns a Jnnn processes to run the selected jobs. Each Jnnn process executes one of the selected jobs. The number of jobs which can run simultaneously is fixed by JOB_QUEUE_PROCESSES  initialization parameter. If the JOB_QUEUE_PROCESSES  parameter is equal to 10, only 10 Jnnn processes could run simultaneously, so only 10 jobs could run in the same time. If JOB_QUEUE_PROCESSES = 0 no jobs could be scheduled for that database. 

How to create a job

To create a job we have to use the DBMS_JOB.SUBMIT procedure.

                  declare

                      vnu_job NUMBER;

                  begin

                      DBMS_JOB.SUBMIT (vnu_job,'scott.Procedure_A;', sysdate,'sysdate+3/(24*60)');

                  end;

                 In this example, the procedure Procedure_A is scheduled to run at every 3 minutes starting from "sysdate".              

How to run a job

                        DBMS_JOB.RUN(vnu_job);

                    end;

                    /

              This PL/SQL block run the previous job at this moment.  

How to change the execution time for a job

                  begin

                       DBMS_JOB.INTERVAL(vnu_job, 'NULL');

            In this example, the job will not run again after it successfully executes and it will be deleted from the job queue.   

How to remove a job from the database 

                   DBMS_JOB.REMOVE(vnu_job);

More information about  this subject ( Managing Oracle Database Jobs ) you can get from  www.in-oracle.com