【statspack安装】

1、创建表空间

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/data/oracle10g/oradata/ccod/system01.dbf

/data/oracle10g/oradata/ccod/undotbs01.dbf

/data/oracle10g/oradata/ccod/sysaux01.dbf

/data/oracle10g/oradata/ccod/7777.dbf

/data/oracle10g/oradata/ccod/ccod.dbf

SQL> create tablespace perf datafile '/data/oracle10g/oradata/ccod/perf.dbf' size 200m autoextend on next 50m maxsize unlimited logging extent management local segment space management auto;

Tablespace created.

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/data/oracle10g/oradata/ccod/system01.dbf

/data/oracle10g/oradata/ccod/undotbs01.dbf

/data/oracle10g/oradata/ccod/sysaux01.dbf

/data/oracle10g/oradata/ccod/7777.dbf

/data/oracle10g/oradata/ccod/ccod.dbf

/data/oracle10g/oradata/ccod/perf.dbf

6 rows selected.

2、安装statspack

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: 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

------------------------------ --------- ----------------------------

7777       PERMANENT

CCOD       PERMANENT

PERF       PERMANENT

SYSAUX       PERMANENT *

Pressing will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: perf

Using tablespace PERF 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.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

。。。。。

SQL> 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.

至此安装完毕！

SQL> show user

USER is "PERFSTAT"

SQL> set pagesize 1000

SQL> select * from tab;

TNAME       TABTYPE CLUSTERID

------------------------------ ------- ----------

V$DYNAMIC_REMASTER_STATS       SYNONYM

STATS$DATABASE_INSTANCE        TABLE

STATS$LEVEL_DESCRIPTION        TABLE

STATS$SNAPSHOT       TABLE

STATS$DB_CACHE_ADVICE       TABLE

STATS$FILESTATXS       TABLE

STATS$TEMPSTATXS       TABLE

STATS$LATCH       TABLE

STATS$LATCH_CHILDREN       TABLE

STATS$LATCH_PARENT       TABLE

STATS$LATCH_MISSES_SUMMARY     TABLE

STATS$LIBRARYCACHE       TABLE

STATS$BUFFER_POOL_STATISTICS   TABLE

STATS$ROLLSTAT       TABLE

STATS$ROWCACHE_SUMMARY       TABLE

STATS$SGA       TABLE

STATS$SGASTAT       TABLE

STATS$SYSSTAT       TABLE

STATS$SESSTAT       TABLE

STATS$SYSTEM_EVENT       TABLE

STATS$SESSION_EVENT       TABLE

STATS$BG_EVENT_SUMMARY       TABLE

STATS$WAITSTAT       TABLE

STATS$ENQUEUE_STATISTICS       TABLE

STATS$SQL_SUMMARY       TABLE

STATS$SQLTEXT       TABLE

STATS$SQL_STATISTICS       TABLE

STATS$RESOURCE_LIMIT       TABLE

STATS$DLM_MISC       TABLE

STATS$CR_BLOCK_SERVER       TABLE

STATS$CURRENT_BLOCK_SERVER     TABLE

STATS$INSTANCE_CACHE_TRANSFER  TABLE

STATS$UNDOSTAT       TABLE

STATS$SQL_PLAN_USAGE       TABLE

STATS$SQL_PLAN       TABLE

STATS$SEG_STAT       TABLE

STATS$SEG_STAT_OBJ       TABLE

STATS$PGASTAT       TABLE

STATS$PARAMETER       TABLE

STATS$INSTANCE_RECOVERY        TABLE

STATS$STATSPACK_PARAMETER      TABLE

STATS$SHARED_POOL_ADVICE       TABLE

STATS$SQL_WORKAREA_HISTOGRAM   TABLE

STATS$PGA_TARGET_ADVICE        TABLE

STATS$JAVA_POOL_ADVICE       TABLE

STATS$THREAD       TABLE

STATS$FILE_HISTOGRAM       TABLE

STATS$TEMP_HISTOGRAM       TABLE

STATS$EVENT_HISTOGRAM       TABLE

STATS$TIME_MODEL_STATNAME      TABLE

STATS$SYS_TIME_MODEL       TABLE

STATS$SESS_TIME_MODEL       TABLE

STATS$STREAMS_CAPTURE       TABLE

STATS$STREAMS_APPLY_SUM        TABLE

STATS$PROPAGATION_SENDER       TABLE

STATS$PROPAGATION_RECEIVER     TABLE

STATS$BUFFERED_QUEUES       TABLE

STATS$BUFFERED_SUBSCRIBERS     TABLE

STATS$RULE_SET       TABLE

STATS$OSSTATNAME       TABLE

STATS$OSSTAT       TABLE

STATS$PROCESS_ROLLUP       TABLE

STATS$PROCESS_MEMORY_ROLLUP    TABLE

STATS$SGA_TARGET_ADVICE        TABLE

STATS$STREAMS_POOL_ADVICE      TABLE

STATS$MUTEX_SLEEP       TABLE

STATS$DYNAMIC_REMASTER_STATS   TABLE

STATS$IDLE_EVENT       TABLE

68 rows selected.

3、删除，执行spdrop.sql即可

SQL> show user

USER is "SYS"

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20553601/viewspace-749203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20553601/viewspace-749203/