[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