Disable SYS Auto Jobs from the Database

 

========================================

DBMS_AUTO_TASK_ADMIN.DISABLE still shows the client_name enabled in DBA_AUTOTASK_CLIENT view (Doc ID 1565768.1)

for Reference: Disable database jobs (DBMS_SCHEDULER , AUTOTASKS and DBMS_JOBS) before upgrade (Doc ID 1614385.1)

1. Check the currently scheduled auto jobs by SYS

SQL> select client_name,status from Dba_Autotask_Client;

CLIENT_NAME STATUS
—————————————————————- ——–
SQL TUNING ADVISOR ENABLED
AUTO OPTIMIZER STATS COLLECTION ENABLED
AUTO SPACE ADVISOR ENABLED

2. Execute the below commands to disable the scheduled jobs

a. exec DBMS_AUTO_TASK_ADMIN.DISABLE(‘AUTO OPTIMIZER STATS COLLECTION’,NULL, NULL);

b. exec DBMS_AUTO_TASK_ADMIN.DISABLE(‘AUTO SPACE ADVISOR’,NULL, NULL);

c. exec DBMS_AUTO_TASK_ADMIN.DISABLE(‘SQL TUNING ADVISOR’,NULL, NULL);

3. Verify

SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME STATUS
—————————————————————- ——–
sql tuning advisor DISABLED
auto optimizer stats collection DISABLED
auto space advisor DISABLED