ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11.2.0.3更改AWR报告底层TYPE类型

11.2.0.3更改AWR报告底层TYPE类型

原创 Linux操作系统 作者:yangtingkun 时间:2012-02-08 23:32:33 0 删除 编辑

11.2.0.2中读取AWR报告的过程在11.2.0.3中报错。

 

 

由于过程太长,将关键部分简化,分别在10.211.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博客 ” ,链接:http://blog.itpub.net/4227/viewspace-715962/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10365841