jeudi 24 avril 2014

How to disable AUTO TASKS under Oracle 11g

Do the following commands :
set lines 180 pages 1000
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

To see the auto task status, use the following command :
 
col client_name for a40
col attributes for a60

select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME                              STATUS   ATTRIBUTES                                                   SERVICE_NAME
---------------------------------------- -------- ------------------------------------------------------------ ----------------------------------------------------------------
auto optimizer stats collection          DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                       DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                       DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL