Step 1: Check the DB name, version, and host name.
NAME OPEN_MODE
--------- ----------
TEST READ WRITE
BANNER
-------------------------------------------------------------
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for HPUX: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Step 2: Check PERFSTAT user exists in the database or not.
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
-------- -------------- ------------------ -------------------- ---------
PERFSTAT OPEN TOOLS TEMP 18-FEB-16
SQL> select sum(bytes/1024/1024) MB from dba_segments where
owner='PERFSTAT';
MB
----------
151.75
Step 3: Check the Synonyms on that user, if exist then drop all
synonyms related to this user otherwise Oracle will not allow to drop this user though using cascade option .
SQL> select synonym_name,table_name,table_owner from dba_synonyms
where table_owner='PERFSTAT';
OBJECT_NAME
OBJECT_TYPE OWNER
STATUS
-------------------
------------------- --------- -------
STATS$SNAPSHOT_ID
SYNONYM PUBLIC
INVALID
STATS$SNAPSHOT_ID
SEQUENCE PERFSTAT VALID
SQL> drop public
synonym STATS$SNAPSHOT_ID;
Step 4: Now drop the user PERFSTAT with
cascade option:
SQL> drop user perfstat cascade;
Step 5: Login as sys user and run the spcreate script..
$ sqlplus "/as sysdba"
SQL> @?/rdbms/admin/spcreate
The above script will ask you three variables in run time....
- default_tablespace=> TOOLS
- temporary_tablespace=>TEMP
- perfstat_password => perf123 (The password must not be the same as username).
check /home location, two logfiles created.....
-rw-r--r-- 1 oracle dba
5618 Oct 29 00:17 spcusr.lis
-rw-r--r-- 1 oracle dba
4677 Oct 29 00:17 spctab.lis
-rw-r--r-- 1 oracle dba
195 Oct 29 00:17 spcpkg.lis
Step 6: After successfully running the script, you need to generate snap ids...Login as perfstat user with password:
SQL> conn perfstat/perf123
SQL> show user
USER is "PERFSTAT"
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL> select
name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot,v$database;
NAME
SNAP_ID Date/Time
--------- ----------
-------------------------
TEST 1 29.10.2015:00:20:47
TEST 2 29.10.2015:00:33:31
Step 7: After snapshots were taken, you can generate performance
reports.
SQL> connect perfstat/perf123
SQL> @?/rdbms/admin/spreport.sql
When the report is run, you are prompted for the following:
The beginning snapshot ID : 1
The ending snapshot ID : 2
The name of the report text file to be created :
report_name.html
Thanks for reading this post ! Please comment if you like this post !
Thank you for your comment !