Search for your Content

Wednesday, July 13, 2011

Disabling and Enabling Oracle Jobs with DBMS_JOB


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.

1 comment: