{"id":655,"date":"2012-07-26T15:17:08","date_gmt":"2012-07-26T06:17:08","guid":{"rendered":"http:\/\/www.whoocus.com\/blog\/?p=655"},"modified":"2012-07-26T15:19:49","modified_gmt":"2012-07-26T06:19:49","slug":"statspack%e3%81%ae%e4%bd%bf%e7%94%a8%e6%ba%96%e5%82%99%e3%80%82","status":"publish","type":"post","link":"https:\/\/www.blowfish.co.jp\/?p=655","title":{"rendered":"StatsPack\u306e\u4f7f\u7528\u6e96\u5099\u3002"},"content":{"rendered":"

StatsPack\u3092\u4f7f\u7528\u3057\u3066\u307f\u3088\u3046\u3068\u8a66\u307f\u308b\u3002
\n[bash]
\nSQL> @$ORACLE_HOME\/rdbms\/admin\/spcreate.sql
\n[\/bash]<\/p>\n

\u3057\u304b\u3057\u30a8\u30e9\u30fc\u3002
\n\u3069\u3046\u3084\u3089\u3001X$KCBFWAIT \u3068\u3044\u3046view\u3092\u53c2\u7167\u3059\u308b\u6a29\u9650\u304c\u306a\u3044\u3089\u3057\u3044\u3002
\n[bash]
\n… Creating views
\ncreate or replace view STATS$X_$KCBFWAIT as select * from X$KCBFWAIT
\n *
\n\u884c1\u3067\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u307e\u3057\u305f\u3002:
\nORA-00942: \u8868\u307e\u305f\u306f\u30d3\u30e5\u30fc\u304c\u5b58\u5728\u3057\u307e\u305b\u3093
\n[\/bash]<\/p>\n

sysdba\u6a29\u9650\u3067\u63a5\u7d9a\u3057\u3066\u307f\u308b\u3002
\n\u3057\u304b\u3057\u3001OS\u30e6\u30fc\u30b6\u304c oralce\u30e6\u30fc\u30b6\u3067\u306a\u3044\u3068sysdba\u6a29\u9650\u304c\u30c0\u30e1\u3089\u3057\u3044\u3002
\n[bash]
\nOracle Database 11g Release 11.2.0.1.0 – 64bit Production
\n\u306b\u63a5\u7d9a\u3055\u308c\u307e\u3057\u305f\u3002
\nSQL> connect \/ as sysdba
\nERROR:
\nORA-01031: \u6a29\u9650\u304c\u4e0d\u8db3\u3057\u3066\u3044\u307e\u3059\u3002
\n[\/bash]<\/p>\n

\u3069\u3046\u3084\u3089\u3001OS\u306eoracle\u30e6\u30fc\u30b6\u3067\u306a\u3044\u3068\u6012\u3089\u308c\u308b\u3088\u3046\u3060\u3002
\n\u4eca\u307e\u3067\u3001\u666e\u901a\u306b\u4f55\u3068\u306a\u304f\u77e5\u3063\u3066\u3044\u308b\u3064\u3082\u308a\u3067Oracle\u306b\u63a5\u3057\u3066\u304d\u305f\u3002\u305d\u306e\u3064\u3051\u3042\u3063\u3066\u3061\u3087\u3063\u3068\u30cf\u30de\u308b\u3002
\n\u53cd\u7701\u3002<\/p>\n

[bash]
\nsu oracle
\n[\/bash]<\/p>\n

\u3053\u3093\u3069\u306fOS\u306e\u30e6\u30fc\u30b6\u3092\u5909\u3048\u3066\u3001system\u30e6\u30fc\u30b6\u3067\u30ed\u30b0\u30a4\u30f3\u3002\u305d\u3057\u3066 sysdba\u3068\u3057\u3066\u63a5\u7d9a\u3002
\n[bash]
\n[oracle@work]$ sqlplus system\/xxxxx<\/p>\n

SQL*Plus: Release 11.2.0.1.0 Production on \u6728 7\u6708 26 14:49:17 2012
\nCopyright (c) 1982, 2009, Oracle. All rights reserved.
\nOracle Database 11g Release 11.2.0.1.0 – 64bit Production
\n\u306b\u63a5\u7d9a\u3055\u308c\u307e\u3057\u305f\u3002
\nSQL> connect \/ as sysdba
\n\u63a5\u7d9a\u3055\u308c\u307e\u3057\u305f\u3002
\n[\/bash]<\/p>\n

\u6c17\u3092\u53d6\u308a\u76f4\u3057\u3066\u518d\u5ea6\u5b9f\u884c\u3002
\n[bash]
\nSQL> @$ORACLE_HOME\/rdbms\/admin\/spcreate.sql
\n[\/bash]<\/p>\n

\u3057\u304b\u3057\u307e\u305f\u30a8\u30e9\u30fc\u3002
\n\u3069\u3046\u3084\u3089\u5148\u7a0b\u306e\u30b9\u30af\u30ea\u30d7\u30c8\u5b9f\u884c\u304c\u4e2d\u9014\u534a\u7aef\u3067\u3001\u30e6\u30fc\u30b6\u3060\u3051\u6b8b\u3063\u3066\u3057\u307e\u3063\u305f\u3088\u3046\u3060\u3002<\/p>\n

[bash]
\n… Creating PERFSTAT user
\ncreate user perfstat
\n *
\n\u884c1\u3067\u30a8\u30e9\u30fc\u304c\u767a\u751f\u3057\u307e\u3057\u305f\u3002:
\nORA-01920: \u30e6\u30fc\u30b6\u30fc\u540d’PERFSTAT’\u306f\u4ed6\u306e\u30e6\u30fc\u30b6\u30fc\u540d\u307e\u305f\u306f\u30ed\u30fc\u30eb\u540d\u3068\u7af6\u5408\u3057\u3066\u3044\u307e\u3059
\nOracle Database 11g Release 11.2.0.1.0 – 64bit Production\u3068\u306e\u63a5\u7d9a\u304c\u5207\u65ad\u3055\u308c\u307e\u3057\u305f\u3002
\n[\/bash]<\/p>\n

\u8ff7\u308f\u305a\u3001drop user\u3002
\n[bash]
\nSQL> drop user perfstat cascade;
\n\u30e6\u30fc\u30b6\u30fc\u304c\u524a\u9664\u3055\u308c\u307e\u3057\u305f\u3002
\n[\/bash]<\/p>\n

\u305d\u3057\u3066\u3001$ORACLE_HOME\/rdbms\/admin\/spcreate.sql \u3092\u5b9f\u884c\u3002
\nPERFSTAT\u30e6\u30fc\u30b6\u304c\u4f5c\u3089\u308c\u308b\u306e\u3067\u3001\u30d1\u30b9\u30ef\u30fc\u30c9\u3068\u304b\u8868\u9818\u57df\u3068\u304b\u4e00\u6642\u8868\u9818\u57df\u3068\u304b\u5c0b\u306d\u3089\u308c\u308b\u3002<\/p>\n

[bash]
\nSQL> @$ORACLE_HOME\/rdbms\/admin\/spcreate.sql<\/p>\n

Choose the PERFSTAT user’s password
\n———————————–
\nNot specifying a password will result in the installation FAILING<\/p>\n

perfstat_password\u306b\u5024\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044: xxxxxx
\nperfstat
\nChoose the Default tablespace for the PERFSTAT user
\n—————————————————
\nBelow is the list of online tablespaces in this database which can
\nstore user data. Specifying the SYSTEM tablespace for the user’s
\ndefault tablespace will result in the installation FAILING, as
\nusing SYSTEM for performance data is not supported.<\/p>\n

Choose the PERFSTAT users’s default tablespace. This is the tablespace
\nin which the STATSPACK tables and indexes will be created.<\/p>\n

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
\n—————————— ——— —————————-
\nDATA1 PERMANENT
\nINDEX1 PERMANENT
\nSET_DTL PERMANENT
\nSET_HST PERMANENT
\nSET_HST2 PERMANENT
\nSET_IDX PERMANENT
\nSET_RP PERMANENT
\nSET_SWK PERMANENT
\nSET_TMP PERMANENT
\nSYSAUX PERMANENT *
\nUSERS PERMANENT<\/p>\n

Pressing <return> will result in STATSPACK’s recommended default
\ntablespace (identified by *) being used.<\/p>\n

default_tablespace\u306b\u5024\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044:<\/p>\n

Using tablespace SYSAUX as PERFSTAT default tablespace.<\/p>\n

Choose the Temporary tablespace for the PERFSTAT user
\n—————————————————–
\nBelow is the list of online tablespaces in this database which can
\nstore temporary data (e.g. for sort workareas). Specifying the SYSTEM
\ntablespace for the user’s temporary tablespace will result in the
\ninstallation FAILING, as using SYSTEM for workareas is not supported.<\/p>\n

Choose the PERFSTAT user’s Temporary tablespace.<\/p>\n

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
\n—————————— ——— ————————–
\nTEMP TEMPORARY *<\/p>\n

Pressing <return> will result in the database’s default Temporary
\ntablespace (identified by *) being used.<\/p>\n

temporary_tablespace\u306b\u5024\u3092\u5165\u529b\u3057\u3066\u304f\u3060\u3055\u3044:<\/p>\n

Using tablespace TEMP as PERFSTAT temporary tablespace.
\n[\/bash]<\/p>\n

\u3059\u308b\u3068\u3001\u5b9f\u884c\u3055\u308c\u308b\u3002
\n[bash]
\n… Creating PERFSTAT user
\n… Installing required packages
\n… Creating views
\n… Granting privileges
\nNOTE:
\nSPCUSR complete. Please check spcusr.lis for any errors.<\/p>\n

SQL>
\nSQL> —
\nSQL> — Build the tables and synonyms
\nSQL> connect perfstat\/&&perfstat_password
\n\u63a5\u7d9a\u3055\u308c\u307e\u3057\u305f\u3002
\nSQL>
\nSQL> set showmode off echo off;<\/p>\n

\u2026\u7701\u7565\u2026<\/p>\n

… Creating STATS$SNAPSHOT_ID Sequence<\/p>\n

\u9806\u5e8f\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002
\n\u30b7\u30ce\u30cb\u30e0\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002
\n… Creating STATS$… tables
\n\u8868\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002
\n\u30b7\u30ce\u30cb\u30e0\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002
\n1\u884c\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002
\n1\u884c\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n

\u2026\u7701\u7565\u2026<\/p>\n

NOTE:
\nSPCTAB complete. Please check spctab.lis for any errors.
\nSQL> — Create the statistics Package
\nSQL> @@spcpkg
\nSQL> Rem
\nSQL> Rem densor.uk 03\/31\/93 – Modified
\nSQL> Rem cellis.uk 11\/15\/89 – Created
\nSQL> Rem
\nSQL>
\nSQL> set echo off;
\nCreating Package STATSPACK…<\/p>\n

\u30d1\u30c3\u30b1\u30fc\u30b8\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n

\u30a8\u30e9\u30fc\u306f\u3042\u308a\u307e\u305b\u3093\u3002
\nCreating Package Body STATSPACK…<\/p>\n

\u30d1\u30c3\u30b1\u30fc\u30b8\u672c\u4f53\u304c\u4f5c\u6210\u3055\u308c\u307e\u3057\u305f\u3002<\/p>\n

\u30a8\u30e9\u30fc\u306f\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n

NOTE:
\nSPCPKG complete. Please check spcpkg.lis for any errors.
\nSQL>
\n[\/bash]<\/p>\n

\u3046\u307e\u304f\u3044\u3063\u305f\u3088\u3046\u3060\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"

StatsPack\u3092\u4f7f\u7528\u3057\u3066\u307f\u3088\u3046\u3068\u8a66\u307f\u308b\u3002 [bash] SQL> @$ORACLE_HOME\/rdbms\/admin\/spcreate.sql [\/bash] \u3057\u304b\u3057\u30a8\u30e9\u30fc\u3002 \u3069\u3046\u3084\u3089\u3001X$KCBFWAIT \u3068 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"saved_in_kubio":false,"footnotes":""},"categories":[12,4],"tags":[],"class_list":["post-655","post","type-post","status-publish","format-standard","hentry","category-oracle","category-tech"],"_links":{"self":[{"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/posts\/655","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=655"}],"version-history":[{"count":2,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/posts\/655\/revisions"}],"predecessor-version":[{"id":657,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=\/wp\/v2\/posts\/655\/revisions\/657"}],"wp:attachment":[{"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=655"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=655"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.blowfish.co.jp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=655"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}