Monday, October 12, 2009

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




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



export PATH

$ORACLE_HOME/bin/sqlplus system/manager<

select * from v$database;

connect perfstat/perfstat

define losnapid=$2

define hisnapid=$3



No comments:

Post a Comment