Kevin's script to get ash report in html format
accept minb prompt 'please input begin time: hh24mi : '
accept mine prompt 'please input end time hh24mi: '
col dbid new_value dbid;
col rname new_value rname;
set verify off
set feed off
set head off
select dbid,name,'c:\logs\'||name||'_ash_'||&&minb||'_'||&&mine||'.html' rname from v$database;
spool &rname
SELECT output
FROM
TABLE(dbms_workload_repository.ASH_REPORT_HTML (&dbid,1,to_date(to_char(sysdate,'DD-MON-YYYY')||':'||'&&minb','dd-mon-yyyy:hh24mi'),to_date(to_char(sysdate,'DD-MON-YYYY')||':'||'&&mine','dd-mon-yyyy:hh24mi')));
spool off
@setup
undefine minb
undefine mine
undefine dbid
Below is a link from internet.
http://yangtingkun.net/?p=552
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.
Comments
Post a Comment