Oracle 10g and higher provides the DBMS_SCHEDULER package to create pre-defined jobs for execution, like executing schedule jobs in UNIX with CRON and in Windows with the SCHEDULER. There are times during maintenance when you need to disable scheduled jobs from executing in order to perform other tasks. The disabling of scheduled jobs is performed by the procedure DBMS_SCHEDULER.DISABLE. In this article we will review the use of DBMS_SCHEDULER in disabling and re-enabling pre-defined Oracle jobs. This procedure will work in Oracle 10g and 11g.
1. Connect to SQL*PLUS as sysdba.
Mylinux:>sqlplus ‘/ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Apr 21 13:05:00 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Execute the following SQL to build the statements to disable the jobs:
“select ‘exec bms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”
SQL> select ‘exec dbms_scheduler.disable(name=>’||””||JOB_NAME||””||’);’
from dba_scheduler_jobs;
‘EXECDBMS_SCHEDULER.DISABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————–
exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.disable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.disable(name=>’PERSTAT_SNAP’);
7 rows selected.
SQL>
3. Take the output from the previous step and execute it.
SQL> exec dbms_scheduler.disable(name=>’PURGE_LOG’);
exec dbms_scheduler.disable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.disable(name=>’GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
4. Execute the following SQL to build the statements to re-enable the jobs.
“select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””||’);’ from dba_scheduler_jobs;”
SQL> select ‘exec dbms_scheduler.enable(name=>’||””||JOB_NAME||””
dba_scheduler_jobs;
‘EXECDBMS_SCHEDULER.ENABLE(NAME=>’||””||JOB_NAME||””||’);’
——————————————————————-
exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);
7 rows selected.
SQL>
5. Take the output from the previous step to re-enable jobs.
SQL> exec dbms_scheduler.enable(name=>’PURGE_LOG’);
exec dbms_scheduler.enable(name=>’FGR$AUTOPURGE_JOB’);
exec dbms_scheduler.enable(name=>’GATHER_STATS_JOB’);
exec dbms_scheduler.enable(name=>’AUTO_SPACE_ADVISOR_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’MGMT_STATS_CONFIG_JOB’);
exec dbms_scheduler.enable(name=>’PERSTAT_SNAP’);
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
SQL>
PL/SQL procedure successfully completed.
…
…
…
That completes disabling and enabling jobs with DBMS_SCHEDULER in 10g and 11g.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com