在11.2.0.2中读取AWR报告的过程在11.2.0.3中报错。
由于过程太长，将关键部分简化，分别在10.2和11.2.0.3中运行：
SQL> SELECT * FROM V$VERSION; BANNER ---------------------------------------------------------------- Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS FOR Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 – Production SQL> DECLARE 2 V_DBID NUMBER; 3 V_BEGIN NUMBER; 4 V_END NUMBER; 5 TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER; 6 V_REPORT T_VARCHAR; 7 BEGIN 8 SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 9 INTO V_DBID, V_BEGIN, V_END 10 FROM DBA_HIST_SNAPSHOT A, V$DATABASE B 11 WHERE A.DBID = B.DBID 12 GROUP BY A.DBID; 13 SELECT OUTPUT 14 BULK COLLECT INTO V_REPORT 15 FROM TABLE( 16 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 17 V_DBID, 18 1, 19 V_BEGIN, 20 V_END, 21 0)); 22 END; 23 / PL/SQL PROCEDURE successfully completed. |
10204上运行没有任何问题，但是在11.2.0.3中：
SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS FOR Solaris: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 – Production SQL> DECLARE 2 V_DBID NUMBER; 3 V_BEGIN NUMBER; 4 V_END NUMBER; 5 TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER; 6 V_REPORT T_VARCHAR; 7 BEGIN 8 SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 9 INTO V_DBID, V_BEGIN, V_END 10 FROM DBA_HIST_SNAPSHOT A, V$DATABASE B 11 WHERE A.DBID = B.DBID 12 GROUP BY A.DBID; 13 SELECT OUTPUT 14 BULK COLLECT INTO V_REPORT 15 FROM TABLE( 16 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 17 V_DBID, 18 1, 19 V_BEGIN, 20 V_END, 21 0)); 22 END; 23 / DECLARE * ERROR at line 1: ORA-06502: PL/SQL: NUMERIC OR VALUE error: Bulk Bind: Truncated Bind ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919 ORA-06512: at line 1 ORA-06512: at line 13 |
同样的代码在不同的版本中表现不同，很显然是Oracle的实现发生的变化。
从错误信息看，报错出现在BULK COLLECT INTO上，那么导致问题的应该是函数的返回值。
SQL> DESC DBMS_WORKLOAD_REPOSITORY FUNCTION ASH_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- L_DBID NUMBER IN L_INST_NUM NUMBER IN L_BTIME DATE IN L_ETIME DATE IN L_OPTIONS NUMBER IN DEFAULT L_SLOT_WIDTH NUMBER IN DEFAULT L_SID NUMBER IN DEFAULT L_SQL_ID VARCHAR2 IN DEFAULT L_WAIT_CLASS VARCHAR2 IN DEFAULT L_SERVICE_HASH NUMBER IN DEFAULT L_MODULE VARCHAR2 IN DEFAULT L_ACTION VARCHAR2 IN DEFAULT L_CLIENT_ID VARCHAR2 IN DEFAULT L_PLSQL_ENTRY VARCHAR2 IN DEFAULT . . . FUNCTION AWR_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- L_DBID NUMBER IN L_INST_NUM NUMBER IN L_BID NUMBER IN L_EID NUMBER IN L_OPTIONS NUMBER IN DEFAULT . . . PROCEDURE MODIFY_SNAPSHOT_SETTINGS Argument Name TYPE IN/OUT DEFAULT? ------------------------------ ----------------------- ------ -------- RETENTION NUMBER IN DEFAULT INTERVAL NUMBER IN DEFAULT TOPNSQL VARCHAR2 IN DBID NUMBER IN DEFAULT |
返回结果为AWRRPT_HTML_TYPE_TABLE类型，查询TYPE类型获取详细信息：
SQL> SET LONG 10000 SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE_TABLE') FROM DUAL; DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE_TABLE') -------------------------------------------------------------------------------- CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE_TABLE" AS TABLE OF AWRRPT_HTML_TYPE SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL; DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE') -------------------------------------------------------------------------------- CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE" AS object (output varchar2(1500 CHAR)) |
可以看到10.2.0.4中，或者说在11.2.0.3以前的版本，TYPE的定义长度是1500 CHAR，而在11.2.0.3中定义变成：
SQL> SET LONG 10000 SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL; DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE') -------------------------------------------------------------------------------- CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE" AS object (output varchar2(8000 CHAR)) |
显然RETURN类型的长度变化导致了这个问题，根据Oracle定义的变化简单修改代码，可以避免11.2.0.3上错误的产生：
SQL> DECLARE 2 V_DBID NUMBER; 3 V_BEGIN NUMBER; 4 V_END NUMBER; 5 TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER; 6 V_REPORT T_VARCHAR; 7 BEGIN 8 SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID) 9 INTO V_DBID, V_BEGIN, V_END 10 FROM DBA_HIST_SNAPSHOT A, V$DATABASE B 11 WHERE A.DBID = B.DBID 12 GROUP BY A.DBID; 13 SELECT OUTPUT 14 BULK COLLECT INTO V_REPORT 15 FROM TABLE( 16 DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML( 17 V_DBID, 18 1, 19 V_BEGIN, 20 V_END, 21 0)); 22 END; 23 / PL/SQL PROCEDURE successfully completed. |