Disable/Enable Maintenance Jobs

Hi all!
A couple days ago a client asked me assistance to disable all the maintenance jobs on DB to run a critical process.

First considerations: We could just disable window changing, once the jobs are related to windows, and Resource Manager Plans use to be as well. However, to answer the question directly:

# To disable/enable all maintenance jobs in from/for all windows:

EXEC DBMS_AUTO_TASK_ADMIN.disable;
EXEC DBMS_AUTO_TASK_ADMIN.enable;

# And to disable/enable specific maintenance jobs from/for all windows:

exec DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.DISABLE('SQL TUNING ADVISOR', NULL, NULL);

exec DBMS_AUTO_TASK_ADMIN.ENABLE('AUTO OPTIMIZER STATS COLLECTION',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('AUTO SPACE ADVISOR',NULL, NULL);
exec DBMS_AUTO_TASK_ADMIN.ENABLE('SQL TUNING ADVISOR', NULL, NULL);

More information and details about it can be taken from here: https://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN11836

Hope it helps. Cheers!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from grepOra

Subscribe now to keep reading and get access to the full archive.

Continue reading