User has no SELECT privilege on V$SESSION（执行计划相关）

开启执行计划的时候，提示如下信息：User has no SELECT privilege on V$SESSION

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

起因：因为权限不够

查询官方文档：PL/SQL Packages and Types Reference 中提示要为V$SQL_PLAN, V$SESSION,V$SQL_PLAN_STATISTICS_ALL赋予select权限。

Security Model
This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

解决方案：

1.登陆SYS用户，将权限赋予SCOTT用户，切换到SCOTT用户

SYS@ orcl>grant select on v_$sql_plan to scott;

Grant succeeded.

SYS@ orcl>grant select on v_$session to scott;

Grant succeeded.

SYS@ orcl>grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

SYS@ orcl>conn scott/tiger
Connected.<strong>
</strong>

2.再次查询执行计划，发现可以正常显示了。但存在告警信息，需要将statistics_level改成all。

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9z4ytb42f97nh, child number 1
-------------------------------------
WARNING: User has no SELECT privileges on V$SQL

Plan hash value: 1838229974

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |    100 |   741K|   741K| 1156K (0)|
|   2 |   TABLE ACCESS FULL| T1   |    100 |       |       |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |    102K|       |       |          |
-----------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic sampling used for this statement

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - Warning: basic plan statistics not available. These are only collected when
:

       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system leve
l



26 rows selected.

3.将 statistics_level改成all之后，出现了新的告警信息（User has no SELECT privileges on V$SQL）

SCOTT@ orcl>alter session set statistics_level=all;

Session altered.

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8vkk4y904h50f, child number 1

WARNING: User has no SELECT privileges on V$SQL

NOTE: cannot fetch plan for SQL_ID: 8vkk4y904h50f, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

4.继续查询官方文档中  DISPLAY_CURSOR Function 的说明，需要额外为SCOTT赋予 V$SQL的select权限。
Usage Notes
To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it will show an appropriate error message.

SYS@ orcl>grant select on v_$sql to scott;

Grant succeeded.

5.再次查询执行计划，问题解决。没有任何告警值了。

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9z4ytb42f97nh, child number 0
-------------------------------------
SELECT /*+leadiing(t1) use_hash(t2) */* FROM t1,t2 WHERE t1.id=t2.t1_id

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |    100 |    100 |00:00:00.92 |    1019 |   741K|   741K| 1141K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |    100 |    100 |00:00:00.01 |       7 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |      1 |    102K|    100K|00:00:00.30 |    1012 |       |       |          |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."T1_ID")

Note
-----
   - dynamic sampling used for this statement

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


23 rows selected.