Oracle statspack无法收集快照

Oracle statspack无法收集快照，及解决办法

SQL> show user

USER is "PERFSTAT"

一： 手动收集一次快照，产生如下错误

SQL> execute statspack.snap
BEGIN statspack.snap; END;

ERROR at line 1:
ORA-01401 : inserted value too large for column
ORA-06512 : at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1

二 查资料：可以删除重新创建 statspack

1 备份 perfstat 用户下所有表
-bash-3.2$ exp perfstat/perfstat file='/home/oracle/perfstat_tab/perf.dmp' owner=perfstat
-bash-3.2$ cd perfstat_tab/
-bash-3.2$ ll -rth
总计 4.9M
-rw-r--r-- 1 oracle oinstall 4.9M 08-30 09:47 perf.dmp
2 删除用户及用户下所有表
SQL> drop user perfstat cascade;
User dropped.
3 执行脚本创建用户，表，指定表空间，产生如下错误
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
ERROR at line 1:
ORA-00955 : name is already used by an existing object --- 说明 statspack 信息没有完全卸载
4 删除收集信息的表空间
SQL> select file_name,tablespace_name,bytes/1024/1024||'M' M from dba_data_files;
/home/oracle/oradata/orcl/statspack01.dbf STATSPACK 100M
RMAN> run{
2> allocate channel d1 type disk format '/home/oracle/perfstat_tab/tbs_%s_%p_%T.sp';
3> backup tablespace statspack filesperset 3;
4> }
SQL> drop tablespace statspack including contents and datafiles;
Tablespace dropped.
5 重新创建表空间
SQL> create tablespace statspack_chen datafile '/home/oracle/oradata/orcl/statspack_chen01.dbf' size 150M;
Tablespace created.
6 重新执行脚本，仍然显示同样的错误
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
ERROR at line 1:
ORA-00955: name is already used by an existing object
7 执行脚本 spdrop.sql ，删除信息
SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql
8 在重新执行 spcreate.sql 脚本，创建用户，表，指定表空间
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
SQL> show user
USER is "PERFSTAT"
SQL> select * from session_privs;
PRIVILEGE

CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM
CREATE SEQUENCE
CREATE PROCEDURE
7 rows selected.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE

SYS SYSTEM
SYSTEM SYSTEM
OUTLN SYSTEM
DBSNMP SYSTEM
CARD APP_DATA
PERFSTAT STATSPACK_CHEN
6 rows selected.

三 重新收集快照，仍然报相同的错误 --- 崩溃了

SQL> execute statspack.snap
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-01401 : inserted value too large for column
ORA-06512 : at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1
SQL> select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
2 3 4 5
HIT RATIO

.72201289

四 查询资料

资料内容：Run statspack.snap report error ora-1401

==========================================================================
----------- 晕 ，又是 bug

五 修改脚本 spcpkg.sql

-bash-3.2$ vim spcpkg.sql
select l_snap_id
, p_dbid
, p_instance_number
, substr (sql_text,1,31)
select l_snap_id
, p_dbid
, p_instance_number
, substrb (sql_text,1,31)
原因： 这个问题只会出现在多位的字符集 ,
substr 会将多位的字符 , 当作一个 byte.
substrb 则会当作多个 byte.
因位 statpack 会将 top 10 的 sql 前 31 个字 存入 table 中 ,
若在 SQL 的前 31 个字有中文，则会有此错误。

六 再次执行脚本，删除 — 重建

SQL> @$ORACLE_HOME/rdbms/admin/spdrop.sql
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql

七 收集快照 ------- 成功！！！

SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') time from stats$snapshot;
SNAP_ID TIME

1 2014-08-30 10:55:48
SQL> execute statspack.snap
PL/SQL procedure successfully completed.
SQL> select snap_id,to_char(snap_time,'yyyy-mm-dd hh24:mi:ss') time from stats$snapshot;
SNAP_ID TIME

1 2014-08-30 10:55:48
2 2014-08-30 11:04:47
SQL> @$ORACLE_HOME/rdbms/admin/spreport.sql
1
2
20140830chen_report.txt
-bash-3.2$ cd perfstat_tab/
-bash-3.2$ ls
20140830chen_report.txt spcpkg.lis spcusr.lis spdusr.lis
perf.dmp spctab.lis spdtab.lis tbs_45_1_20140830.sp
-bash-3.2$ vim 20140830chen_report.txt
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host

ORCL 1278871612 orcl 1 9.0.1.0.0 NO server1
Snap Id Snap Time Sessions Curs/Sess Comment

Begin Snap: 1 30-Aug-14 10:55:48 48 5.9
End Snap: 2 30-Aug-14 11:04:47 48 6.3
Elapsed: 8.98 (mins)
Cache Sizes (end)

Buffer Cache: 92M Std Block Size: 4K
Shared Pool Size: 128M Log Buffer: 400K
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.15 In-memory Sort %: 99.61
Library Hit %: 99.33 Soft Parse %: 97.63
Execute to Parse %: 21.12 Latch Hit %: 100.00
…………………………………….

**转载自**[chenoracle](http://blog.itpub.net/29785807/viewspace-1260544/)