User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL

User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL User has no SELECT privilege on V$SESSION

error 


 select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));

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



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



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

====

Grant below to user  !!!


grant select on v_$sql_plan to scott ;

grant select on v_$session to scott ;

grant select on v_$sql_plan_statistics_all to scott ;

grant select on v_$sql to scott ;

===

Now all good .


set linesize 150 pagesize 300
select * from table(dbms_xplan.display_cursor(null,null,format=>'ADVANCED +ALLSTATS LAST, IOSTATS -PROJECTION -OUTLINE'));SQL> SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8trj2kacqhm6f, child number 0
-------------------------------------
select count(*) from t where a = 42 and b=42

Plan hash value: 2966233522

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |    22 (100)|          |      1 |00:00:00.01 |      75 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |      75 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |      1 |     6 |    22   (0)| 00:00:01 |    100 |00:00:00.01 |      75 |
---------------------------------------------------------------------------------------------------------------------