联系:手机/微信(+86 17813235971) QQ(107644445)
标题:解决Statspack报告时Snap Id为”#####”
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
生成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