[20211129]更新dpcawr1.sql脚本.txt

[20211129]更新dpcawr1.sql脚本.txt --//想使用dbms_xplan.display_awr查看旧执行计划的outline，发现我写的脚本有问题，做一个更新并记录： 1.环境： > @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production > @ desc_proc sys dbms_xplan display_awr INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME         OBJECT_NAME SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED ---------- -------------------- ----------- -------- -------------------- -------------------- --------- ---------- SYS        DBMS_XPLAN           DISPLAY_AWR        1                      TABLE                OUT       N                                                    3 SQL_ID               VARCHAR2             IN        N                                                    4 PLAN_HASH_VALUE      NUMBER               IN        Y                                                    5 DB_ID                NUMBER               IN        Y                                                    6 FORMAT               VARCHAR2             IN        Y $ cat dpcawr1.sql set verify off select * from table(dbms_xplan.display_awr(NVL('&1',NULL),nvl(&3,NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost')); > @ dpcawr1 avgpwqjsdrgzp outline '' select * from table(dbms_xplan.display_awr(NVL('avgpwqjsdrgzp',NULL),nvl(,NULL),NULL,'ALLSTATS LAST PEEKED_BINDS outline cost'))                                                                          * ERROR at line 1: ORA-00936: missing expression --//参数3是数字，我带入字符'',替换出错，我这样写法要加入单引号。 $ cp  dpcawr1.sql  dpcawrx.sql $ cat dpcawrx.sql set verify off select * from table(dbms_xplan.display_awr(NVL('&1',NULL),nvl('&3',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost')); > @ dpcawrx avgpwqjsdrgzp outline '' --//执行ok，单独带入PLAN_HASH_VALUE测试也可以执行，字符自动转换数字。 --//顺便贴一下我写的dpc.sql脚本： $ cat dpc.sql set verify off --select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition')); select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2')); prompt prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive prompt