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博客

[ORACLE] 清除statspack数据

最新文章