Statspack

To implement statspack:

alter session set tracefile_identifier=
Note ids 149124.1, 149121.1 , 149113.1
Steps for installing statspack:
1) Bring down application services
2) Check for invalid objects
3) Log on as sysdba (sqlplus "/ as sysdba")
4) Run the following script:
@ $ORACLE_HOME/rdbms/admin/sprecreate.sql
Provide the perfstat user password (keep it same as username) and the default tablespace (PERFSTAT - already created) and temporary tablespace (TEMP) when prompted.
5) Check the spcreate.lis file to if there are any errors
6) Check to see if there any more invalids than there were before installing. If there are then recompille the invlaid objects.
7) Log on as the perfstat user
8) Execute the following package:
SQL> exec statspack.snap();
9) Make sure the package completes without errors.
10) Bring the application services back up

How to install statspack
Shut down Applications
Create the PERFSTAT tablespace ( Ensure perfstat user’s default tablespace is set to this tablespace during install)
Install the statspack utility
Verify Install
bring up the Application Services
The detailed level steps for statspack install is below:
SQL> CREATE TABLESPACE perfstat
DATAFILE '/d05/oracle/visdata/perfstat02.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K max size 4096m
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
Install Statspack. At the prompt specify PERFSTAT password. Keep it same as username. Also give
default tablespace as perfstat tablespace and temporary tablespace as TEMP.
SQL> @$ORACLE_HOME/rdbms/admin/spcreate.sql
Verfiy its been installed properly
SQL> connect perfstat/perfstat
SQL> exec statspack.snap(i_snap_level=>10);


steps for dropping perfstat user and tablespace:

DROP the PERFSTAT USER by running the following sql from the DATABASE ORACLE HOME
sqlplus '/as sysdba';
@$ORACLE_HOME/rdbms/admin/spdrop.sql

2.drop package body SYS.STATSPACK_EXT_NEW;

3.drop trigger SYS.LOG_ERRORS_TRIG;

4.DROP TABLESPACE PERF_EXT including contents and datafiles;