11.2.0.3更改AWR报告底层TYPE类型
在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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-715962/ ，如需转载，请注明出处，否则将追究法律责任。
