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

[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);