User has no SELECT privilege on V$SESSION

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. Otherwise you will end up with below error.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
SQL> 

Solution:

If you are getting this error from scott , then grant following permission to scott.

SQL> GRANT SELECT ON v_$session TO scott;
Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.

SQL> grant select on v_$sql_plan to scott;
Grant succeeded.

SQL> grant select on v_$sql to scott;
Grant succeeded.

-Asifkhan P.