如何删除statspack生成的历史数据

    当用$ORACLE_HOME/rdbms/admin/spauto.sql创建一个默认的job以后，数据库中会每1个小时收集一次快照，方便我们可以随时用$ORACLE_HOME/rdbms/admin/spreport.sql产生report诊断分析报告，但是采样的数据量是相当惊人的，如果忽略了这个job，或者没有定期的检查剩余空间大小，有的生产环境很可能会由于空间不足到导致其它的不可预测状况，所以我们要定期的删除statspack收集的历史数据，前提条件是要做好备份，然后删除不再需要的历史数据，删除方法有2种：手工删除和自动脚本删除。 

1.备份perfstat用户下的对象 
[oracle@rac1 ~]$ export NLS_LANG=american_america.ZHS16GBK 
[oracle@rac1 ~]$ cd /data 
[oracle@rac1 ~]$ expperfstat/oracle@leizfile=./perfstat_backup.dmp wner=perfstat 
[oracle@rac1 ~]$ ll -t 
-rw-r--r--   1 oracle     dba        1893620736 Apr 27 10:40 perfstat_backup.dmp 

2.删除statspack生成的历史数据 
  a：手工删除statspack中的历史记录 
         1)保留最近1个月的数据 
            delete from perfstat.stats$snapshot where snap_time <= add_months(trunc(sysdate),-1) 
            --2520条记录 
            --1313.869s 
    备注：删除2520条记录，需要25分钟，这是因为delete from stats$snapshot会及联删除代snap_id的所有表中相关snap_id的记录，所以需要的时间会很长（但是stats$undostat,stats$sqltext除外: 
    删除STATSPACK数据可以使用DELETE STATS$SNAPSHOT的方法，除了STATS$UNDOSTAT之外，其他的包含SNAP_ID的表都会被清除掉。不过PERFSTAT用户下还有一些表不包含SNAP_ID： 
   select b.segment_name, sum(b.bytes)/1024/1024 
   from user_segments b 
   where b.segment_name in (SELECT TABLE_NAME FROM USER_TABLES 
   MINUS 
   SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'SNAP_ID') 
   group by b.segment_name 
   order by 2 
     table_name                                                M 
    ------------------------------------------------      ------ 
     STATS$IDLE_EVENT                              0.125 
     STATS$LEVEL_DESCRIPTION            0.125 
     STATS$STATSPACK_PARAMETER     0.125 
     STATS$SEG_STAT_OBJ                        1 
     STATS$SQLTEXT                                     176 

         2)清空非关联删除表(stats$undostat,stats$sqltext) 
            TRUNCATE TABLE PERFSTAT.stats$undostat 
            --19440条记录 
           TRUNCATE TABLE PERFSTAT.stats$sqltext 
           --8060778条记录 
           备注：由于PERFSTAT.stats$sqltext中的记录数量很大，所以一般选择truncate，当然也可以delete一个月前的数据，但是速度会相当的慢。 
       delete from stats$undostat where begin_time <= add_months(trunc(sysdate),-1) 
       delete from stats$sqltext bb  where hash_value in (select a.hash_value from stats$sqltext  a,stats$sql_summary b where a.hash_value = b.hash_value(+) and b.hash_value is null ) 
       
  b：oracle提供自动脚本删除statspack中的历史记录 
      除了手工删除历史记录以外,oracle还提供了系统脚本用户truncate这些统计信息表， 
      $ORACLE_HOME//rdbms/admin/sptrunc.sql 
       truncate table STATS$FILESTATXS; 
       truncate table STATS$TEMPSTATXS; 
       truncate table STATS$LATCH; 
       truncate table STATS$LATCH_CHILDREN; 
       truncate table STATS$LATCH_MISSES_SUMMARY; 
       truncate table STATS$LATCH_PARENT; 
       truncate table STATS$LIBRARYCACHE; 
       truncate table STATS$BUFFER_POOL_STATISTICS; 
       truncate table STATS$ROLLSTAT; 
       truncate table STATS$ROWCACHE_SUMMARY; 
       truncate table STATS$SGA; 
       truncate table STATS$SGASTAT; 
       truncate table STATS$SYSSTAT; 
       truncate table STATS$SESSTAT; 
       truncate table STATS$SYSTEM_EVENT; 
       truncate table STATS$SESSION_EVENT; 
       truncate table STATS$BG_EVENT_SUMMARY; 
       truncate table STATS$WAITSTAT; 
       truncate table STATS$ENQUEUE_STATISTICS; 
       truncate table STATS$SQL_SUMMARY; 
       truncate table STATS$SQL_STATISTICS; 
       truncate table STATS$SQLTEXT; 
       truncate table STATS$PARAMETER; 
       truncate table STATS$RESOURCE_LIMIT; 
       truncate table STATS$DLM_MISC; 
       truncate table STATS$UNDOSTAT; 
       truncate table STATS$SQL_PLAN; 
       truncate table STATS$SQL_PLAN_USAGE; 
       truncate table STATS$SEG_STAT; 
       truncate table STATS$SEG_STAT_OBJ; 
       truncate table STATS$DB_CACHE_ADVICE; 
       truncate table STATS$PGASTAT; 
       truncate table STATS$INSTANCE_RECOVERY; 
       truncate table STATS$JAVA_POOL_ADVICE; 
       truncate table STATS$THREAD; 
       truncate table STATS$CR_BLOCK_SERVER; 
       truncate table STATS$CURRENT_BLOCK_SERVER; 
       truncate table STATS$INSTANCE_CACHE_TRANSFER; 
       truncate table STATS$FILE_HISTOGRAM; 
       truncate table STATS$TEMP_HISTOGRAM; 
       truncate table STATS$EVENT_HISTOGRAM; 
       truncate table STATS$TIME_MODEL_STATNAME; 
       truncate table STATS$SYS_TIME_MODEL; 
       truncate table STATS$SESS_TIME_MODEL; 
       truncate table STATS$STREAMS_CAPTURE; 
       truncate table STATS$STREAMS_APPLY_SUM; 
       truncate table STATS$PROPAGATION_SENDER; 
       truncate table STATS$PROPAGATION_RECEIVER; 
       truncate table STATS$BUFFERED_QUEUES; 
       truncate table STATS$BUFFERED_SUBSCRIBERS; 
       truncate table STATS$RULE_SET; 
       truncate table STATS$OSSTAT; 
       truncate table STATS$OSSTATNAME; 
       truncate table STATS$PROCESS_ROLLUP; 
       truncate table STATS$PROCESS_MEMORY_ROLLUP; 
       truncate table STATS$STREAMS_POOL_ADVICE; 
       truncate table STATS$SGA_TARGET_ADVICE; 
       truncate table STATS$MUTEX_SLEEP; 
       truncate table STATS$DYNAMIC_REMASTER_STATS; 
      
       delete from STATS$SNAPSHOT; 
       delete from STATS$DATABASE_INSTANCE; 
      
       commit; 
此时，与statspack相关的大部分系统表都会被清空，如果采样了大量的数据，直接delete是非常缓慢的，所以可以考虑使用oracle提供的默认脚本清空perfstat中的历史记录。