Sharing My Database Experience

Menu

How to delete or 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
/