Oracle provides the ability to schedule predetermined jobs via the package DBMS_JOB, just as you would in a UNIX environment with CRON or the Windows environment with SCHEDULER. Normally, a production database will have several dozen pre-scheduled jobs executing at various times of the day. There are times when you will need to disable a JOB or all jobs due to various events. This article explains the use of procedure DBMS_JOB.BROKEN which allows you to disable a job and re-enable it once you are finished. This procedure will work in Oracle 8, 8i, 9i, 10g, and 11g.

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 – Production on Tue May 25 10:19:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com