User has no SELECT privilege on V$SESSION

                   User has no SELECT privilege on V$SESSION

When other database users want to store plan table and they want to see the explain plan for cursor level or AWR level or etc. They should have some privileges.


Step1 : Create user

sqlplus  "/as sysdba"

SQL> create user dpusr identified by dpuser;
SQL> grant connect,resource to dpuser;


Step 2: Connect the user

$sqlplus dpusr/dpusr
SQL>@?/rdbms/admin/utlxplan.sql

SQL>explain plan for select * from EMPLOYEES;

Explain created.



SQL> select * from table(dbms_xplan.display_cursor());

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

User has no SELECT privilege on V$SESSION


or

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

User has no SELECT privilege on V$SQL_PLAN


it means that user is missed privileges.

Step3:  Grant the privileges


SQL> GRANT SELECT ON v_$session TO dpusr;

Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to dpusr;

Grant succeeded.

SQL> grant select on v_$sql_plan to dpusr;


Grant succeeded.


SQL> grant select on v_$sql todpusr;

Grant succeeded;



Step 4:  Again 

 sqlplus dpusr/dpusr;

SQL>select * from table(dbms_xplan.display_cursor());

 Now it works .