how to drop and installing the statspack

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

Staspack drop:-

Checking the objects in PREFSTAT tablespace.

select table_name from dba_tables where tablespace_name='PERFSTAT';

select object_name,object_type from dba_objects where object_name='STATSPACK';

 set lines 300
 col OWNER for a12
 col SEGMENT_NAME for a30
 col SEGMENT_TYPE for a20
 set pages 300
 select owner,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='PERFSTAT'; 

spdrop.sql is used to drop user, tables and package for  performance diagnostic tool STATSPACK

@?/rdbms/admin/spdrop.sql

Now all statspack data has been dropped.So now we can drop the PREFSTAT tablespace.

drop tablespace perfstat including contents and datafiles;

Installing statspack:-


spcreate.sql  SQL*PLUS command file which creates the STATSPACK user, tables and package for the performance diagnostic tool STATSPACK


Before running spcreate.sql we need to create prefstat tablespace for holding statspack user , tables and package for the performance diagnostic tool

create tablespace PERFSTAT datafile '/oracle/data11/perfstat01.dbf' size 2g;


The first step in the installation is the creation of the PERFSTAT user, which owns all PL/SQL code and database objects created, including the Statspack tables, constraints, and the Statspack package. During installation, you are prompted for the PERFSTAT user's password, default tablespace, and temporary tablespace. The default tablespace is used to create all Statspack objects, such as tables and indexes. The temporary tablespace is used for sort-type activities.

@?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat
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
------------------------------ --------- ----------------------------
KABA                           PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

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

Using tablespace TEMP as PERFSTAT temporary tablespace.
 

 Comment me if you find this post is useful also let me know if you need any new topics.