Disable or enable auto task jobs in oracle database

Automatic database maintenance tasks is used by oracle for tunning SQL queries, gather stale or non existing statistics on objects and space advisory. 

These Automatic maintenance jobs is configured default in Oracle Database :

• Automatic Optimizer Statistics Collection - Gathers stale or missing statistics
• Automatic Segment Advisor – Identifies segments that reorganized to save space
• Automatic SQL Tuning Advisor – Tune high load SQL

How to disable automatic jobs in oracle?

To disable all these jobs you can :

begin
DBMS_AUTO_TASK_ADMIN.disable;
end;
/

How to enable automatic jobs in oracle?

begin
DBMS_AUTO_TASK_ADMIN.enable;
end;
/

How to disable sql tuning advisor, auto space advisor and auto optimizer stats collection separately one by one:

SQL> SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                           STATUS
-------------------------------       ---------
auto optimizer stats collection       ENABLED
auto space advisor                    ENABLED
sql tuning advisor                    ENABLED

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

PL/SQL procedure successfully completed.

3. Check the status again for auto task jobs

SQL> SELECT client_name, status FROM dba_autotask_client;

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

How to enable sql tuning advisor, auto space advisor and auto optimizer stats collection separately one by one:

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);

SQL> EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);

PL/SQL procedure successfully completed.