Search This Blog

Monday, May 7, 2007


Below step for generate statspack Report

1. User must have SYSDBA privs

set ORACLE_SID=orcl
sqlplus sys/pwd as sysdba

2.You should create SEPRATE TABLESPACE for statspack report.

create tablespace PERFSTAT
datafile 'c:\perf01.dbf' size 100m
autoextend on
segment management auto;

We should generate STATSPACK report DURING PEAK TIME
run SPCREATE.SQL script
Located : $ORACLE_HOME\rdbms\admin\spcreate.sql

conn sys as sysdba

4.connect with PERFSTAT user
perfstat user create automatically when we run SPCREATE.SQL script

conn perfstat/password STATSPACK.SNAP procedure

exec statspack.snap;

Note: After 15 minutes run again STATSPACK.SNAP procedure SPREPORT for generate statspack report in text format.


7.connect with SYSDBA privs user ( sys user)
and execute SPDROP.SQL script for drop PERFSTAT user and all related tables

conn sys as sysdba


conn sys as sysdba
drop tablespace [TABLESPACE NAME]
including contents and datafiles

Note: analyze your report

1 comment:

Emmanuel said...

The tablespace creation command should use the "segment space management auto" syntax