[20230818]建立打开关闭autotask脚本.txt

--//链接提供建立与关闭autotask脚本
https://logic.edchen.org/how-to-disable-autotask/
https://logic.edchen.org/how-to-enable-autotask/

--//如果分别建立要写2个脚本分别如下,是否可以合并成一个呢?
--//建立如下脚本task_disable.sql:
column stmt format a90;
select 'exec dbms_auto_task_admin.disable(''' || CLIENT_NAME || ''', null, null);' stmt from dba_autotask_client where status = 'ENABLED';

--//建立如下脚本task_enable.sql:
column stmt format a90;
select 'exec dbms_auto_task_admin.enable(''' || CLIENT_NAME || ''', null, null);' stmt from dba_autotask_client where status = 'DISABLED';

--//建立脚本task.sql如下:
column stmt format a90;
column client_name format a35;
column status format a10;
column ATTRIBUTES format a56;
column WINDOW_GROUP format a20
select client_name, status,attributes,window_group from dba_autotask_client;
select 'exec dbms_auto_task_admin.'||decode(status,'ENABLED','disable','DISABLED','enable')||'(''' || CLIENT_NAME || ''', null, null);' stmt from dba_autotask_client;

--//当然我仅仅输出命令，并没有执行，再copy and paste就可以了。
--//简单测试：
1.环境：
SYS@test> @ ver1
PORT_STRING          VERSION        BANNER                                                                               CON_ID
-------------------- -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.测试
SYS@test> @ task
CLIENT_NAME                         STATUS     ATTRIBUTES                                               WINDOW_GROUP
----------------------------------- ---------- -------------------------------------------------------- --------------
sql tuning advisor                  ENABLED    ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL   ORA$AT_WGRP_SQ
auto optimizer stats collection     ENABLED    ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_OS
auto space advisor                  ENABLED    ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_SA

STMT
------------------------------------------------------------------------------------------
exec dbms_auto_task_admin.disable('sql tuning advisor', null, null);
exec dbms_auto_task_admin.disable('auto optimizer stats collection', null, null);
exec dbms_auto_task_admin.disable('auto space advisor', null, null);

--//copy and paste并执行如下：
SYS@test> exec dbms_auto_task_admin.disable('auto space advisor', null, null);
PL/SQL procedure successfully completed.

SYS@test> @ task
CLIENT_NAME                         STATUS     ATTRIBUTES                                               WINDOW_GROUP
----------------------------------- ---------- -------------------------------------------------------- --------------------
sql tuning advisor                  ENABLED    ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL   ORA$AT_WGRP_SQ
auto optimizer stats collection     ENABLED    ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_OS
auto space advisor                  DISABLED   ON BY DEFAULT, VOLATILE, SAFE TO KILL                    ORA$AT_WGRP_SA

STMT
------------------------------------------------------------------------------------------
exec dbms_auto_task_admin.disable('sql tuning advisor', null, null);
exec dbms_auto_task_admin.disable('auto optimizer stats collection', null, null);
exec dbms_auto_task_admin.enable('auto space advisor', null, null);