In Oracle : Manage Oracle Database Jobs

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Manage Oracle Database Jobs

 

 Manage Oracle Database Jobs

 

 

Here are the main topics for this article:

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

 

                    begin

                        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');

                  end;

                  /

            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 

 

                begin

                   DBMS_JOB.REMOVE(vnu_job);

                end;

                /

            

 

View the status of the running jobs

 

select * from DBA_JOBS_RUNNING;

 

 

View more information about the database jobs 

 

select * from DBA_JOBS;

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Manage Oracle Database Jobs

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.