一、statspack是什么？

二、安装

三、使用

我在这里讲一下，它安装过程吧，很简单

我的环境是os：win7旗舰版；oracle：10.2.0.1

1、SQL> conn /as sysdba ---一定要sysdba连接才能有权安装
已连接。

2、在执行3中的脚本之前要准备，确定以下信息

（1）perfstat模式使用的密码，我设定为和模式一样

（2）perfstat使用的默认表空间

（3）perfstat使用的临时表空间，这里使用系统临时表空间temp

---创建perfstat表空间

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST.DBF
E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\N9MYL.ORA

已选择7行。

SQL> create tablespace perfstat datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\perfstat.dbf' size 2000m;

表空间已创建。

3、cd E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN ---找到脚本spcreate.sql执行之

---执行结果

SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\spcreate.sql

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

输入 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
------------------------------ --------- ----------------------------
EXAMPLE                        PERMANENT
N9MYL                          PERMANENT
PERFSTAT                       PERMANENT
SYSAUX                         PERMANENT *
TEST                           PERMANENT
USERS                          PERMANENT

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

输入 default_tablespace 的值: perfstat

Using tablespace PERFSTAT 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 will result in the database's default Temporary
tablespace (identified by *) being used.

输入 temporary_tablespace 的值: temp

Using tablespace temp as PERFSTAT temporary tablespace.

..........

该脚本内容如下：

-- Create PERFSTAT user and required privileges
@@spcusr

--
-- Build the tables and synonyms
connect perfstat/&&perfstat_password
@@spctab
-- Create the statistics Package
@@spcpkg

包含了三个脚本spcusr.sql、spctab.sql、spcpkg.sql

4、如何卸载statspack，利用spdrop.sql删除用户（spdusr.sql）和视图(spdtab.sql)

内容如下：

-- Drop PERFSTAT's tables and indexes

@@spdtab--
-- Drop PERFSTAT user

@@spdusr

这些脚本都会生成安装日志，放置路径是该sql*plus的运行环境路径

ITPub博客

配置statspack

最新文章