oracle snapid,【学习笔记】Oracle Statspack报告中的Snap Id为”#####”的解决办法

天萃荷净

运维DBA反映在查看数据库Statspack报告时，发现snap id为####，结合MOS找到解决办法

1.生成Statspack报告时候发现Snap Id为”#####”

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

----------- -------- ------------ ------------ ------------

631690435 1 VODAPP vodapp T5440-1

Enter value for dbid: 631690435

Using 631690435 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ----- ----------------- ----- ----------------------

vodapp VODAPP ##### 16 Oct 2012 17:00 5

##### 16 Oct 2012 18:00 5

##### 16 Oct 2012 19:00 5

##### 16 Oct 2012 20:00 5

##### 16 Oct 2012 21:00 5

##### 16 Oct 2012 22:00 5

##### 16 Oct 2012 23:00 5

…………

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap:

因为没有办法定位到Snap Id,所以暂时无法准确的输入对应值,当然可以通过如下sql查询相应的Snap Id

select SNAP_ID,to_char(SNAP_TIME,'yyyy-mm-dd hh24:mi:ss') SNAP_TIME

from STATS$SNAPSHOT order by SNAP_ID;

虽然可以通过这个方面来曲线解决这个问题,但是还有比较完善一点的解决方法,我们阅读spcreate.sql相关脚本,修改相关程序来实现

--通过spcreate.sql发现

spcreate.sql调用@@sprepins

--编辑sprepins.sql

column instart_fmt noprint;

column inst_name format a12 heading 'Instance';

column db_name format a12 heading 'DB Name';

column snap_id format 9990 heading 'Snap|Id';

-->(修改为)column snap_id format 999990 heading 'Snap|Id';

column snapdat format a17 heading 'Snap Started' just c;

column lvl format 99 heading 'Snap|Level';

column commnt format a22 heading 'Comment';

再次生成sp报告

SQL> @?/rdbms/admin/sprepins.sql

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

----------- -------- ------------ ------------ ------------

631690435 1 VODAPP vodapp T5440-1

Enter value for dbid: 631690435

Using 631690435 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Completed Snapshots

Snap Snap

Instance DB Name Id Snap Started Level Comment

------------ ------------ ------- ----------------- ----- ----------------------

vodapp VODAPP 58916 16 Oct 2012 17:00 5

58917 16 Oct 2012 18:00 5

58918 16 Oct 2012 19:00 5

58919 16 Oct 2012 20:00 5

58920 16 Oct 2012 21:00 5

58921 16 Oct 2012 22:00 5

58922 16 Oct 2012 23:00 5

58923 17 Oct 2012 00:00 5

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle Statspack报告中的Snap Id为”#####”的解决办法