Setting up Oracle Statistics Package (STATSPACK)

To install statspack – you must be connected to database as SYSDBA and execute the script spcreate.sql located under $ORACLE_HOME/rdbms/admin. Additionally, you need have:

• the password you would like to set for PERFSTATS schema
• the default tablespace for PERFSTAT schema
• the temporary tablespace for PERFSTAT schema

If you run it through SQL*Plus the output will be similar to below:

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 13 07:15:23 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @spcreate.sql

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

Enter value for perfstat_password: xxxxxxxxxxxxx
xxxxxxxxxxxxx

Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
... output removed for brevity ...
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
... output removed for brevity ...
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
... output removed for brevity ...
NOTE:
SPCTAB complete. Please check spctab.lis for any errors.
... output removed for brevity ...
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.

The script will create spc*.lis files under the directory you started SQL*Plus; examine this for any errors

To uninstall statspack – connect to database as SYSDBA and run spdrop.sql located under $ORACLE_HOME/rdbms/admin.

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 13 07:30:45 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @spdrop
... output removed for brevity ...
Dropping old versions (if any)
Synonym dropped.
Sequence dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
NOTE:
SPDTAB complete. Please check spdtab.lis for any errors.
... output removed for brevity ...
SQL> --  Drop PERFSTAT user
... output removed for brevity ...
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.

The script will create spd*.lis files under the directory you started SQL*Plus; examine this for any errors