Setting Up Statspack
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/QS2007@orcl 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
sys@orcl>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/QS2007@orcl 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/QS2007@orcl 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
sys@orcl>@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
.......
.......
.......
perfstat@ORCL>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.