Snapshot Purging

Recently we had to purge the snapshot as we were getting threshold alert. But Purging did not brought down the usage. Inorder to reduce threshold we had to resize the datafile of the perfstat.



When sppurge.sql is executed, all available snapshots are displayed and prompts for losnapid (low snap id ) & hisnapid (hi snap id). All snapshots between these will be deleted.




Purging requires large rollback segment. Commit command or exiting from sql will automatically commit the purging.




You can set the transaction to use particular rollback segment. Also exporting perfstat schema will ideal before purging, truncation or dropping.



SQL> @$ORACLE_HOME/rdbms/admin/sppurge




SQL > delete from stats$snapshot where snap_id <>


SQL > delete from stats$sql_summary where snap_time <>


Batch file


#!/bin/ksh

ORACLE_SID=$1

export ORACLE_SID

ORACLE_HOME=`cat /etc/oratabgrep ^$ORACLE_SID:cut -f2 -d':'

`export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH

export PATH

$ORACLE_HOME/bin/sqlplus system/manager<

select * from v$database;

connect perfstat/perfstat

define losnapid=$2

define hisnapid=$3

@sppurge

exit!