Resolving "User has no SELECT privilege on V$SESSION" Error in Oracle

 When working with Oracle databases, you might encounter the error User has no SELECT privilege on V$SESSION while trying to use the dbms_xplan.display_cursor function with a schema other than sysdba privilege. This error occurs because the user lacks the necessary privileges to access certain views. In this blog post, we'll guide you through the steps to grant these privileges and successfully run your queries.

Problem Scenario

As the user "USER_PERF1", you attempt to display the execution plan of a SQL statement using dbms_xplan.display_cursor, but you encounter the following error:

SQL> show user
USER is "PERF_USER1"


select * from perf_demo_test(dbms_xplan.display_cursor);


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

Solution: Grant Necessary Privileges

To resolve this issue, you need to grant the required SELECT privileges to the user "USER_PERF1". You can do this by logging in as a privileged user, such as "SYS" or "SYSTEM", and executing the necessary GRANT commands.

Step-by-Step Instructions

Log in as a privileged user & Grant the required privileges to the user "USER_PERF1":

$ sqlplus / as sysdba
Connected.

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

SQL> GRANT SELECT ON v_$sql_plan_statistics_all TO USER_PERF1;
Grant succeeded.

SQL> GRANT SELECT ON v_$sql_plan TO USER_PERF1;
Grant succeeded.

SQL> GRANT SELECT ON v_$sql TO USER_PERF1;
Grant succeeded.

Verify the Solution

After granting the necessary privileges, you can verify the solution by running the "dbms_xplan.display_cursor" function again.

SQL> show user
USER is "PERF_USER1"

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  76hvnakasaq12, child number 0
-------------------------------------
select /*+ perftest */ count(*), max(col2) from perf_demo_tab where flag = :n

Plan hash value: 7564017273

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name 			 | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      			 |       |       |   182 (100)|          |
|   1 |  INDEX SCAN	   |      			 |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| PERF_DEMO_TAB               | 28943 |  2138K|   182   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------


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

   2 - filter("FLAG"=:N)

Conclusion 

By granting the necessary SELECT privileges on v_$session, v_$sql_plan_statistics_all, v_$sql_plan, and v_$sql to the user "SCOTT", you can resolve the error and successfully use dbms_xplan.display_cursor to display execution plans. This ensures that the user has the appropriate access to the dynamic performance views required for monitoring and troubleshooting SQL execution plans.

Remember, it's crucial to manage privileges carefully to maintain database security and integrity. 

Happy querying!