How to grant select access to v$session to other users?

In my previous articles I have given some of the most important issues coming in the oracle. In this article I would like to give brief steps of most common task from DBA – How to grant select access to v$session system view to other users? This is the most common scenario in day to day life. PL/SQL developer’s always requires to analyses and monitor the multiple sessions coming from applications. With using this sessions developers will tackle the issue soon. But most of the time they do not have access to v$session view and requires the access. In this situation developers will ask the DBA’s to provide the select access to monitor those sessions.

What you will see in this article?

Need of V$Session view

How to grant select access to v$session to other users?

Need of V$Session view :

You can see detailed description about V$SESSION view on oracle website but i would like to give you few bullet points to remember :

1. V$SESSION view will give us the multiple running sessions information on oracle.
2. If DBA wants to see which running sessions are on waiting state then this view is important.
3. Using this view DBA can see the long running sessions and they can take actions on those sessions.
4. You can use the following important query to check the owner of the session and give the grant to that owner accordingly.

SELECT owner, object_type FROM dba_objects WHERE object_name = 'V$SESSION';

These are some important points to remember!!

How to grant select access to v$session to other users?

In this section we can see how to grant select access to v$session view step by step :

Step 1 : Important Point to remember

We can not grant directly to system view v$session as this is the synonym in oracle.

Example :

If you directly try to give the select grant to view then following error will occure.

Query :

 GRANT SELECT ON v$session TO Complexsql;

grant select on v$session to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The above error will come.

Step 2 : You can check type of object v$Session

Query :

SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;

OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM

Step 3 : Find the object name ( View/table) associated with the synonym.

select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;

TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION

Step 4 : One can give the grant to V$SESSION as view or as table

You can use following statement :

GRANT SELECT ON V_$SESSION TO Complexsql;

Grant succeeded.

These are multiple steps to grant select access to v$session.

I hope you get clear idea regarding the grant access to v$session as this is synonym. You can also check multiple system tables. If you like this article or if you have issues with the same kindly comment in comments section.