Statspack : Liberar Espacio Recreandolo

Cuando tomamos estadisticas con STATSPACK si no tenemos la precaucion de ir depurando (Ver en mi blog como DEPURAR) va a ir creciendo la cantidad de estadisticas y por mas que despues depuremos tendriamos que liberar el espacio.
Un metodo rapido para liberar espacio del tablespace PERFSTAT si es que no necesitamos conservar las estadisticas historicas es re.crearlo.


1 - Borrar todo el Statspack
SQL> @?/rdbms/admin/spdrop

2 - Eliminar el Tablespace
SQL> drop tablespace perfstat including contents;

3 - Eliminar los Datafiles
/u01/oradata/ORCL>rm perfstat01.dbf
/u01/oradata/ORCL>rm perfstat02.dbf

4 - Recrear tablespace
SQL> create tablespace PERFSTAT datafile '/u01/oradata/ORCL/perfstat01.dbf' size 2048M;

5 - Para recrearlo, ejecutar spcreate. Nos va a pedir que ingresemos : password para el ususario PERFSTAT, Tablespace Default y Temporary Tablespace

SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE

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

PERFSTAT                      PERMANENT

SYSAUX                          PERMANENT *

UNDO                              PERMANENT

USERS                             PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE

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

TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

.............................................................

Creating Package STATSPACK...

Package created.

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>

Listo!

Scripts Para Depurar Snapshots Statspack

Scripts para depurar un rango de snapshots dejando las estadisticas de los ultimos 30 dias. Se recomienda ejecutar diariamente.


depura_statspack.sh

#!/bin/bash

################################################
# Depura las estadisticas del statspack dejando los ultimos 30 dias.  
#
#################################################

source ~/.bash_profile
sqlplus perfstat/perfstat @$HOME/scripts/depura_statspack.sql
exit

____________________________________________________________

depura_statspack.sql
spool /home/oracle/logs/depura_statspack.log
col menor format 999999 new_value losnapid
col mayor format 999999 new_value hisnapid
select min(snap_id) as menor, max(snap_id) as mayor from  stats$snapshot
where snap_time < sysdate - 30;
@?/rdbms/admin/sppurge
spool off
exit