CDBとPDBの間で迷子になりそう PART3 - SQL*PlusのAutotrace編 Tweet

今回迷子になったのは、昔からあるSQL*PlusのAutotraceで.

Autotraceの使い方は、$ORACLE_HOME/sqlplus/admin/plustrce.sqlをSYSユーザーで実行し、PLUSTRACEロール作成されたら必要なユーザーへ該当ロールを付与。
あとは、SQL*Plusでautotraceを有効にする。

これだけです。

が、

マルチテナント（シングルテナント含む）化した途端に、迷子になりそうな場面に出会います！
（これにハマったことのあるかたは、#ローカルロール！　ってハッシュタグでtw...しなくてもいいですw）

マルチテナントでは、共通ユーザー、共通ロール、ローカルユーザー、ローカルロールという２つのタイプのユーザーとロールが登場しました。
CDB$ROOT/各PDBのディクショナリービュー同様、頭では理解したつもりでも指が勝手タイプして迷子になっちゃいうこともあるw　（実はOracleさんも？。。。だったりしてね。

余談はこれぐらいにして、とにかく試して見ましょう。

バージョンは以下のとおり

SYS@orcl12c> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE	12.1.0.2.0	Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

マルチテナントにしてあります。（シングルテナントでも同じですが）

SYS@orcl12c> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL12C                     READ WRITE NO
         4 PDBORCL12CLONED                READ WRITE NO

今日の主役、共通ユーザを作成します。
共通ユーザーとするためには、c##（デフォルト）という接頭子をユーザ名に付加する必要があります。（なれるまで辛いw）

SYS@orcl12c> create user c##hoge identified by hoge
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users
  5  container=all;

ユーザーが作成されました。

CDB$ROOT含む全PDBにC##HOGEユーザが作成されました。
（実はシステム定義の管理ユーザーは共通ユーザー扱になっていることも忘れちゃいけないんですよね。おそらく同じ癖というか特徴はあるはず）

SYS@orcl12c> select con_id,username,common from containers(dba_users) where username in ('SYS','C##HOGE')

    CON_ID USERNAME                       COM
---------- ------------------------------ ---
         1 SYS                            YES
         1 C##HOGE                        YES
         4 SYS                            YES
         4 C##HOGE                        YES
         3 SYS                            YES
         3 C##HOGE                        YES

※さりげなく、PDB CONTAINER句（赤字部分）を使ってますが、もともとそれで遊びたかっただけなんです。

今後のお遊び向けにいくつかのシステム権限の付与...

SYS@orcl12c> grant create session ,resource to c##hoge container=all;

権限付与が成功しました。

SYS@orcl12c> grant create table, create view to c##hoge container=all;

権限付与が成功しました。

SYS@orcl12c> grant create synonym to c##hoge container=all;

権限付与が成功しました。

共通ユーザーでもSQL*Plusのautotrace使いたいな〜と。
（最近はアダプティブな実行計画の影響で影が薄いAutotraceやExplain planですが、とりあえず見たい時や、実行統計を軽く見たいときには便利なんで）

と、思ったとことから、迷子になりまして、、はいw

CDB$ROOTの共通ユーザー接続して、autotraceを有効にしようとしました。
みなれたエラーがでるわけですよ。ロール作ってないので：）

”SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。”

SYS@orcl12c> conn c##hoge/hoge
接続されました。
C##HOGE@orcl12c> show con_name

CON_NAME
------------------------------
CDB$ROOT

C##HOGE@orcl12c> 
C##HOGE@orcl12c> set autot on
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

いつものように、$ORACLE_HOME/sqlplus/admin/plustrce.sqlを実行してPLUSTRACEロールを作成しようとすると。。。。。
なんと、ロールが作成できません！！！

そりゃ、そうですよね。CDB$ROOTには共通なものが載るわけですから、。。。
マルチテナント化してあるので、ロールも共通ロールを作成する必要があります。。。オラクルさん。。。。痛恨のミス？！w

PLUSTRACEというロールをCDB$ROOTに作成しようとするとローカルロールを作成している扱いになるので、共通ロールにしてねというエラーが返されます。

どうすんだよwこれw
と、初っ端から迷子ですw　（MOSは未確認。なにか記載されてそうな気はしますがw）

C##HOGE@orcl12c> conn / as sysdba
接続されました。
SYS@orcl12c> @?/sqlplus/admin/plustrce
SYS@orcl12c> 

...中略...

1SYS@orcl12c> create role plustrace;
create role plustrace
            *
行1でエラーが発生しました。:
ORA-65096: 共通ユーザーまたはロール名が無効です

共通ロールにしろというんだから、素直に共通ロールを作ってみます。
$ORACLE_HOME/sqlplus/admin/plustrce.sqlを元に、ロール名をPLUSTRACEからC##PLUSTRACEに変更したスクリプトを作成しました。

共通ロールについては、以下も参照のこと。
Oracle® Databaseセキュリティ・ガイド　12cリリース1 (12.1) 共通ロールの作成

以下のように、C##（共通ユーザやロールのデフォルト接頭子）を付加し共通ユーザ向けロールを作成すればできるようになりますよね！。使う機会があるかどうかは別ですがw

C##HOGE@orcl12c> !cat $ORACLE_HOME/sqlplus/admin/plustrce_common.sql 

drop role c##plustrace;
create role c##plustrace;

grant select on v_$sesstat to c##plustrace;
grant select on v_$statname to c##plustrace;
grant select on v_$mystat to c##plustrace;
grant c##plustrace to dba with admin option;

C##PLUSTRACEの作成ログ
うまくいきました！！！

SYS@orcl12c> drop role c##plustrace;
drop role c##plustrace
          *
行1でエラーが発生しました。:
ORA-01919: ロール'C##PLUSTRACE'は存在しません

SYS@orcl12c> create role c##plustrace;

ロールが作成されました。

SYS@orcl12c> grant select on v_$sesstat to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant select on v_$statname to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant select on v_$mystat to c##plustrace;

権限付与が成功しました。

SYS@orcl12c> grant c##plustrace to dba with admin option;

権限付与が成功しました。

作成したautotrace用共通ロールをcontainter=allで共通ユーザに付与したのですが。。。。
実は、container=allとしても。PDB側では今まで通り、$ORACLE_HOME/sqlplus/admin/plustrce.sqlを各PDBのSYSユーザー実行し、PLUSTRACEロール（ローカルロール）をPDBに存在する共通ユーザ個別に付与する必要があるようです。（面倒くさい＞＜）

SYS@orcl12c> grant c##plustrace to c##hoge container=all;

C##HOGE@orcl12c> conn / as sysdba
接続されました。
SYS@orcl12c> 
SYS@orcl12c> conn c##hoge/hoge
接続されました。
C##HOGE@orcl12c> show con_name

CON_NAME
------------------------------
CDB$ROOT
C##HOGE@orcl12c> set autot trace exp stat
C##HOGE@orcl12c> select * from dual;

実行計画
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

PDB側のC##HOGEユーザーには繼承されない。。。。。かるく迷子になってる状況。

C##HOGE@orcl12c> conn c##hoge/hoge@pdborcl12c
接続されました。
C##HOGE@pdborcl12c> 
C##HOGE@pdborcl12c> show con_name

CON_NAME
------------------------------
PDBORCL12C
C##HOGE@pdborcl12c> set autot trace exp stat
SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

各PDBのローカルユーザーは、以前からあるplustrce.sqlを各PDBごとに個別に作成、付与する必要があるみたい...もう完全に迷子w。
（本当はこれをネタしたかったわけじゃなかったんですけどね、トホホ）

C##HOGE@pdborcl12c> conn sys@pdborcl12c as sysdba
パスワードを入力してください: 
接続されました。
SYS@pdborcl12c> 
SYS@pdborcl12c> 
SYS@pdborcl12c> show con_name

CON_NAME
------------------------------
PDBORCL12C
SYS@pdborcl12c> 
SYS@pdborcl12c> @?/sqlplus/admin/plustrce

...中略...

SYS@pdborcl12c> conn c##hoge/hoge@pdborcl12c
接続されました。
C##HOGE@pdborcl12c> set autot trace exp stat
C##HOGE@pdborcl12c> select * from dual;


実行計画
----------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

統計
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

次回、本当に試したかったことへ、つづく....（予定）
久々にその機能本気で使うのかって、ネタになりそうで。。。いいかも（こんな展開は予想してなかっただけに；）

以上、はじめての、 Yahoo! Japan Coworking Space LODGE　より

---

関連エントリー
FAQ になりそうな、12c MTA環境での統計履歴管理　ー　CDBとPDBの間で迷子になりそう　PART2