Setting Up Statspack

> Database > Oracle Database

Statspack must be installed when connected as sysdba (Connect / as sysdba).

You'll need to know three pieces of information before running the script spcreate.sql (spdrop.sql to drop it) :

  • the password you would like to use for the PERFSTAT schema that will be created
  • the default tablespace you would like to use for PERFSTAT
  • the temporary tablespace you would like to use for PERFSTAT

And perform the following :

C:\oracle\product\10.2.0\db_1\sqlplus\demo>set ORACLE_SID=ORCL
C:\oracle\product\10.2.0\db_1\sqlplus\demo>sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 26 12:25:54 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

[email protected]>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\oracle\product\10.2.0\db_1\sqlplus\demo>cd C:\oracle\product\10.2.0\db_1\sqlplus\sqlpath
The system cannot find the path specified.

C:\oracle\product\10.2.0\db_1\sqlplus\demo>set SQLPATH=C:\oracle\product\10.2.0\db_1\sqlplus\sqlpath

C:\oracle\product\10.2.0\db_1\sqlplus\demo>sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 26 13:11:35 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

idle>exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\oracle\product\10.2.0\db_1\sqlplus\demo>sqlplus sys/[email protected] as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 26 13:15:07 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

[email protected]>@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spcreate

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
------------------------------ --------- ----------------------------
ADCI_DATA                      PERMANENT
EXAMPLE                        PERMANENT
GLOBAL                         PERMANENT
INDX                           PERMANENT
OLAP_VIEWS                     PERMANENT
QUICKSTART                     PERMANENT
SH_OLAP                        PERMANENT
SYSAUX                         PERMANENT *
TBSALERT                       PERMANENT
USERS                          PERMANENT

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

Enter value for default_tablespace:

Using tablespace SYSAUX 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
------------------------------ --------- --------------------------
GLOBAL_TEMP                    TEMPORARY
OLAP_VIEWS_TEMP                TEMPORARY
SH_OLAP_TEMP                   TEMPORARY
TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace.


... Creating PERFSTAT user


... Installing required packages


... Creating views

.......
.......
.......

[email protected]>set echo off;
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.

Documentation / Reference