SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB1 READ WRITE NO
SQL> alter session set container=ORCLPDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL> SHOW PARAMETER timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level string TYPICAL
statistics_level string TYPICAL
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
SQL> alter session set container=ORCLPDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL> show parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
SQL> CREATE TABLESPACE PERFSTAT
2 DATAFILE 'データベースファイルのパス' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;
Tablespace created.
Statspack専用の表領域が作成できたら、下記のSELECT文を実行して確認できます。
set pages 300
set lines 300
col tablespace_name for a50
col NOW_SIZE[MB] for 9999999999
col USED_SIZE[MB] for 9999999999
col USED_PER for 999990.99
SELECT
df.tablespace_name,
SUM(df.bytes/(1024*1024)) as "NOW_SIZE[MB]",
SUM((df.bytes-NVL(fs.free_bytes,0))/(1024*1024)) as "USED_SIZE[MB]",
ROUND(SUM((df.bytes-NVL(fs.free_bytes,0))/(1024*1024))/SUM(df.bytes/(1024*1024)) * 100,2) as "USED_PER"
FROM
dba_data_files df
LEFT JOIN (
SELECT
file_id,
SUM(bytes) as free_bytes
FROM
dba_free_space
GROUP BY
file_id
) fs
ON
df.file_id = fs.file_id
GROUP BY
df.tablespace_name
;
SQL> @?/rdbms/admin/spcreate
Session altered.
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
-------------------------------------------------- --------------------- ----------------------------
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for 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 <return> 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 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>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;
Session altered.
SQL> set pages 300
SQL> set lines 300
SQL> col object_type for a20
SQL> SELECT
2 object_type,
3 count(*)
4 FROM
5 user_objects
6 GROUP BY
7 object_type
8 ORDER BY
9 object_type
10 ;
OBJECT_TYPE COUNT(*)
-------------------- ----------
INDEX 73
PACKAGE 1
PACKAGE BODY 1
SEQUENCE 1
TABLE 73
VIEW 1
6 rows selected.
SQL> col object_name for a50
SQL> col object_type for a20
SQL> col status for a15
SQL> SELECT
2 object_name,
3 object_type,
4 status
5 FROM
6 user_objects
7 WHERE
8 status != 'VALID'
9 ORDER BY
10 object_type,
11 object_name
12 ;
no rows selected
これでOracle Statspackのインストールまでは完了です。
PERFSTATユーザに権限付与
自動取得ジョブを登録する上で、PERFSTATユーザにCREATE JOB権限が必要です。
12c以降は自分自身のスキーマにジョブを作成する場合でもCREATE JOB権限が必要です。
SQL> connect / as sysdba
Connected.
SQL> alter session set container = orclpdb1;
Session altered.
SQL> grant create job to perfstat;
Grant succeeded.
