【statspack安裝】

sub8412發表於2012-11-14
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章