How to delete or truncate Statspack data?

One can truncate data in STATSPACK or delete range of snaps stored.

To truncate STATSPACK data

Using $ORACLE_HOME/rdbms/admin/sptrunc.sql, one can truncate STATSPACK data. To run the script you will need to connect as PERFSTAT user.

perfstat@TESTDB> @?/rdbms/admin/sptrunc.sql

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables. You may
wish to export the data before continuing.

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. &'exit&'), otherwise if you would like to begin
the truncate operation, press

Enter value for begin_or_exit:

To delete STATSPACK data – This will prompt for starting snap id and end snap id.
One can run the following SQL that will show all the snap ids on the current instance.
select s.snap_id
, to_char(s.snap_time,&' dd Mon YYYY HH24:mi:ss&') snap_date
, s.baseline
, s.snap_level #level#
, di.host_name host_name
, s.ucomment
from perfstat.stats$snapshot s
, perfstat.stats$database_instance di
, v$database v
, v$instance i
where s.dbid = v.dbid
and di.dbid = v.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.startup_time = s.startup_time
order by di.db_name, i.instance_name, s.snap_id
/

SNAP_ID SNAP_DATE B level
———- ——————— – ———-
HOST_NAME
—————————————————————-
UCOMMENT
——————————————————————————–
1 27 Dec 2011 18:30:56 5
MACHINE
2 27 Dec 2011 18:30:58 6
MACHINE

One thought on “How to delete or truncate Statspack data?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s