Disable and Enable Auto task Job for 11g and 12c version in Oracle

 https://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN12333

Enabling and Disabling Maintenance Tasks for all Maintenance Windows

You can disable a particular automated maintenance task for all maintenance windows with a single operation. You do so by calling the DISABLE procedure of the DBMS_AUTO_TASK_ADMIN PL/SQL package without supplying the window_name argument. For example, you can completely disable the Automatic SQL Tuning Advisor task as follows:

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

To enable this maintenance task again, use the ENABLE procedure, as follows:

BEGIN
  dbms_auto_task_admin.enable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

Disable and Enable Auto task Job for 11g and 12c version in Oracle

Automated database maintenance tasks is used by oracle to auto tune the SQL Queries, stale stats gather and space advisory. Some time this jobs change the execution plan and caused performance issue.

Following jobs is configured default by Oracle:
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

Disable all three jobs, you can used following command:

For Disable:
EXEC DBMS_AUTO_TASK_ADMIN.disable;

For Enable:
EXEC DBMS_AUTO_TASK_ADMIN.enable;


Disable one by one follow following commands:

1. Check the enabled job present in oracle database

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

2. Disable the following jobs

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

4. Enable the auto task jobs:

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.