DBA Notes: 2011/10/20

Cheng Li

Truncate Stats pack data

For DBA routine operation, we meet a warning as following:

Issue:

Subject: EVENT Oracle DB Kenan Prod storage events WARNING

Name : Oracle DB Kenan Prod storage events Target Name : CTMCAT Target Type : Database Node Name : kenandb.ctm.plc.cwintra.com Status : WARNING

Tablespace Full

Tablespace TOOLS is 95.66% full.  22720.0 KBytes of free space remain.

WARNING

Oct 19 2011 06:31:06 AM

This tablespace is allocated for stats pack usage.

Solution:

SQL> conn perfstat/perfstat@ctmcat

Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

CTMCAT

SQL> @$ORACLE_HOME/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:

Entered  at the 'begin_or_exit' prompt

Beginning truncate operation

Table truncated.

1159 rows deleted.

1 row deleted.

Commit complete.

Package altered.

Truncate operation complete

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning option JServer Release 9.2.0.7.0 - Production $

Reference: