Follow below steps for disabling auto tasks in oracle database.
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor ENABLED
auto optimizer stats collection ENABLED
sql tuning advisor ENABLED
set lines 180 pages 1000
col client_name for a40
col attributes for a60
select client_name, status,attributes,service_name from dba_autotask_client
/
-- DISABLING SPACE ADVISOR
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
-- DISABLING AUTO SQL TUNING ADVISOR
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
-- DISABLING AUTO OPTIMIZER STATS
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
SQL>
SQL> select client_name,status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto space advisor DISABLED
auto optimizer stats collection DISABLED
sql tuning advisor DISABLED
