基表にSELECT権限がないVIEWに対して実行計画を取得する

基表に対してSELECT権限がないVIEWに対してEXPLAIN PLAN FORを使用して実行計画を取得しようとすると

SQL> explain plan for select * from test.test1_view;
explain plan for select * from test.test1_view
                                           *
行1でエラーが発生しました。:
ORA-01039: ビューの基礎オブジェクトに対する権限が不十分です。

基表にSELECT権限があればEXPLAIN PLAN FORも問題なくできますが、
基表にアクセスさせたくないためにVIEWしたので基表にSELECT権限付与することなく実行計画を取得する方法を考えた。

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR())を使えばできることがわかった。
ただし、DBMS_XPLANパッケージを使用するにいくつかの動的ディクショナリビューにSELECT権限を付与することが必要。

DBMS_XPLAN

DBMS_XPLANパッケージは、パッケージ所有者(SYS)ではなく、コール・ユーザーの権限で実行されます。テーブル・ファンクションDISPLAY_CURSORを使用するには、V$SQL_PLAN、V$SESSIONおよびV$SQL_PLAN_STATISTICS_ALL.の各固定ビューに対するSELECT権限が必要です。
DISPLAY_CURSOR機能を使用する場合、コール・ユーザーには、固定ビューV$SQL_PLAN_STATISTICS_ALL、V$SQLおよびV$SQL_PLANに対するSELECT権限が必要です。権限がない場合は、エラー・メッセージが表示されます。

権限を付与する。

grant select on V_$SESSION to texplain;
grant select on V_$SQL_PLAN to texplain;
grant select on V_$SQL_PLAN_STATISTICS_ALL to texplain;
grant select on V_$SQL to texplain;

試す。

SQL>select count(*) from test.test1_view;

  COUNT(*)
----------
       100

SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT   SQL_ID  0fn9jb84xq0a4, child number 0
-----------------   -------------------------------------
select count(*) from test.test1_view

Plan hash value: 2932712509

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| TEST1 |   100 |  2600 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   

無事取得完了。でもこうすると実際にクエリが発行されちゃうのと基表名とかはわかっちゃうのか。。
あと、クエリの実行キャンセルしても実行計画を取得できるからいいね。

SQL>select count(*) from test.test1_view where id=1;
select count(*) from test.test1_view where id=1;
                   *
行1でエラーが発生しました。:
ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました


SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
-----------------------------------------------
SQL_ID  5twf49nt4gjdw, child number 0
-------------------------------------
select count(*) from puli.test1_view where id=1

Plan hash value: 1249660229

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE    |              |     1 |    13 |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0061810 |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)