Home > ISSUES > User has no SELECT privilege on V$SESSION

User has no SELECT privilege on V$SESSION

March 29, 2014

Today when i was working on a support request, i was asked to get the explain plan after execution of the SQL using below SQL.

======================================================

<Execute the Problem Sql Statement>
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));

======================================================

When i run above command, i am getting below error.

SQL> select * from table(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
——————————————————————————–
User has no SELECT privilege on V$SESSION

Solution is as follows.

grant the select privs on

v_$session, v_$sql_plan_statistics_all,v_$sql_plan, v_$sql to user. so the problem will be resolved.

to give select, run below command.

 

grant select on v_$sql to <username>;

 

grant select on v_$sql_plan to <username>;

 

grant select on v_$sql_plan_statistics_all to <username>;

 

grant select on v_$session to <username>;

 

Advertisements
Categories: ISSUES
%d bloggers like this: