Enable/Disable JOBS during Maintenance
Contents
___________________________________________________________________________________________________
Disable Before Maintenance
1. Capture values
2. Disable ALL DBMS_JOBS
3. Disable ALL DBMS_SCHEDULER JOBS
Enable After Maintenance
4. Enable ALL DBMS_JOBS
5. Enable ALL DBMS_SCHEDULER JOBS
___________________________________________________________________________________________________
Disable Before Maintenance
-- For DBMS_JOBS SYS @ CDB2 > show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 4000 <----- SYS @ CDB2 > -- FOR DBMS_SCHEDULER JOBS SYS @ CDB2 > COL ATTRIBUTE_NAME FOR A40 SYS @ CDB2 > COL VALUE FOR A60 SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE; ATTRIBUTE_NAME VALUE ---------------------------------------- ------------------------------------------------------------ MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Europe/Vienna EMAIL_SERVER EMAIL_SERVER_ENCRYPTION NONE EMAIL_SERVER_CREDENTIAL EMAIL_SENDER LAST_OBSERVED_EVENT 6F4E2CF66A235446E055000000000001::19440::SCHED::0001 EVENT_EXPIRY_TIME FILE_WATCHER_COUNT 0 CURRENT_OPEN_WINDOW 11 rows selected. <---- SYS @ CDB2 >
To disable jobs created by dbms_jobs set job_queue_processes to zero. SYS @ CDB2 > alter system set job_queue_processes=0 scope=both sid='*'; System altered. SYS @ CDB2 > show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 0 <------- SYS @ CDB2 >
3. Disable ALL DBMS_SCHEDULER JOBS
SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE'); PL/SQL procedure successfully completed. SYS @ CDB2 > SYS @ CDB2 > col ATTRIBUTE_NAME for a30 SYS @ CDB2 > col VALUE for a60 SYS @ CDB2 > set lines 180 SYS @ CDB2 > set pages 999 SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE; ATTRIBUTE_NAME VALUE ------------------------------ ------------------------------------------------------------ MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Europe/Vienna EMAIL_SERVER EMAIL_SERVER_ENCRYPTION NONE EMAIL_SERVER_CREDENTIAL EMAIL_SENDER LAST_OBSERVED_EVENT 6F4E2CF66A235446E055000000000001::19440::SCHED::0001 EVENT_EXPIRY_TIME FILE_WATCHER_COUNT 0 CURRENT_OPEN_WINDOW SCHEDULER_DISABLED TRUE <--------- 12 rows selected. <----- SYS @ CDB2 >
Enable After Maintenance
SYS @ CDB2 > alter system set job_queue_processes=4000 scope=both sid='*'; System altered. SYS @ CDB2 > show parameter job_queue_processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 4000 <------- SYS @ CDB2 >
5. Enable ALL DBMS_SCHEDULER JOBS
SYS @ CDB2 > exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE'); PL/SQL procedure successfully completed. SYS @ CDB2 > SYS @ CDB2 > col ATTRIBUTE_NAME for a30 SYS @ CDB2 > col VALUE for a60 SYS @ CDB2 > set lines 180 SYS @ CDB2 > set pages 999 SYS @ CDB2 > select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE; ATTRIBUTE_NAME VALUE ------------------------------ ------------------------------------------------------------ MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Europe/Vienna EMAIL_SERVER EMAIL_SERVER_ENCRYPTION NONE EMAIL_SERVER_CREDENTIAL EMAIL_SENDER LAST_OBSERVED_EVENT 6F4E2CF66A235446E055000000000001::19440::SCHED::0001 EVENT_EXPIRY_TIME FILE_WATCHER_COUNT 0 CURRENT_OPEN_WINDOW 11 rows selected. <------ SYS @ CDB2 >
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com