DBMS_AUTO_TASK_ADMIN.DISABLE
In the previous post, we've talked about how to modify the starting time and duration of maintenance windows to make the scheduled AUTOTASK work at a latter time.
Theoretically, the shift would avoid accidental resource contention with batch jobs, but you may also consider to turn all AUTOTASK off to stop any suspicion on it.
Let's see the steps.
Check AutoTask
Here we query DBA_AUTOTASK_CLIENT for sure.
SQL> column client_name format a35;
SQL> column status format a10;
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
There're 3 AUTOTASK jobs are enabled.
Turn off AutoTask
First, we compose disabling statements like this.
SQL> set linesize 100;
SQL> column stmt format a90;
SQL> select 'exec dbms_auto_task_admin.disable(''' || CLIENT_NAME || ''', null, null);' stmt from dba_autotask_client where status = 'ENABLED';
STMT
------------------------------------------------------------------------------------------
exec dbms_auto_task_admin.disable('auto space advisor', null, null);
exec dbms_auto_task_admin.disable('auto optimizer stats collection', null, null);
exec dbms_auto_task_admin.disable('sql tuning advisor', null, null);
Then we execute them individually.
SQL> exec dbms_auto_task_admin.disable('auto space advisor', null, null);
PL/SQL procedure successfully completed.
SQL> exec dbms_auto_task_admin.disable('auto optimizer stats collection', null, null);
PL/SQL procedure successfully completed.
SQL> exec dbms_auto_task_admin.disable('sql tuning advisor', null, null);
PL/SQL procedure successfully completed.
Check AutoTask Again
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
They are all disabled. To enable AUTOTASK jobs back, we just revert the process.