[ORACLE] 清除statspack数据

1） 修改sppurge.sql脚本

prompt Snapshots for this database instance
prompt ====================================

select s.snap_id
     , s.snap_level                                      "level"
     , to_char(s.snap_time,' dd Mon YYYY HH24:mi:ss')    snap_date
     , di.host_name                                      host_name
     , s.ucomment
  from stats$snapshot s
     , stats$database_instance di
 where s.dbid              = :dbid
   and di.dbid             = :dbid
   and s.instance_number   = :inst_num
   and di.instance_number  = :inst_num
   and di.startup_time     = s.startup_time
 order by db_name, instance_name, snap_id;


---增加-------------
column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
  from stats$snapshot s
     , stats$database_instance di
 where s.dbid              = :dbid
   and di.dbid             = :dbid
   and s.instance_number   = :inst_num
   and di.instance_number  = :inst_num
   and di.startup_time     = s.startup_time
   and s.snap_time < sysdate-60;
---------------------


--
--  Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id's specified, for the database instance
prompt you are connected to.

2）删除 stats$snapshot 数据表中的相应数据，其他表中的数据会相应的级连删除

3）Oracle 还提供了系统脚本用于Truncate 这些统计信息表，这个脚本名字是: sptrunc.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/58054/viewspace-627149/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/58054/viewspace-627149/