ksaitoの日記

日々試したことの覚え書き

statspack

Oracleの統計情報を取るためのstatspackのインストール方法です。

インストール

aptパッケージでインストールしたOracle 10gでの設定です。
statspack用ユーザのパスワードとデフォルトテーブルスペース、一時テーブルスペースの指定が必要です。

$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 21 00:17:22 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> @?/rdbms/admin/spcreate.sql

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

Enter value for perfstat_password: 
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
--------------------------- ----------------------------
SYSAUX
PERMANENT                   *

USERS
PERMANENT


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

Enter value for default_tablespace: SYSAUX

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
--------------------------- --------------------------
TEMP
TEMPORARY                   *


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

Enter value for temporary_tablespace: TEMP

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.

SQL>