DBMS_AUTO_TASK_ADMIN.ENABLE
To enable AUTOTASK back to work, we just revert the process of disabling AUTOTASK jobs.
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 DISABLED
auto optimizer stats collection DISABLED
auto space advisor DISABLED
There're 3 AUTOTASK jobs are disabled.
Turn on AutoTask
First, we compose enabling statements like this.
SQL> set linesize 100;
SQL> column stmt format a90;
SQL> select 'exec dbms_auto_task_admin.enable(''' || CLIENT_NAME || ''', null, null);' stmt from dba_autotask_client where status = 'DISABLED';
STMT
----------------------------------------------------------------------------------------------------
exec dbms_auto_task_admin.enable('auto space advisor', null, null);
exec dbms_auto_task_admin.enable('auto optimizer stats collection', null, null);
exec dbms_auto_task_admin.enable('sql tuning advisor', null, null);
Then we execute them individually.
SQL> exec dbms_auto_task_admin.enable('auto space advisor', null, null);
PL/SQL procedure successfully completed.
SQL> exec dbms_auto_task_admin.enable('auto optimizer stats collection', null, null);
PL/SQL procedure successfully completed.
SQL> exec dbms_auto_task_admin.enable('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 ENABLED
auto optimizer stats collection ENABLED
auto space advisor ENABLED
They are all back to work.