One can’t grant direct access V$session as v$session is a synonym.
SQL> GRANT SELECT ON v$session TO scott;
grant select on v$session to test
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
— shows the V$SESSION is a public synonym
SQL> SELECT owner, object_type FROM dba_objects WHERE object_name = ‘V$SESSION’;
OWNER OBJECT_TYPE
———————— ——————-
PUBLIC SYNONYM
— shows the object (table/view) the synonym points to
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;
TABLE_OWNER TABLE_NAME
————– ——————————
SYS V_$SESSION
But one can grant access the underlying table/view.
SQL> GRANT SELECT ON V_$SESSION TO scott;
Grant succeeded.
Note: The same steps can be used to find other V$ views and access can be granted depending on internal representation.
An Oracle Spin by Alex Lima 
Yes its working fine Thanks…….!!!!!!
But it is possible to grant select on V_$SESSION to role and than grant this role to user without dba role ?
Yes, it should work, i have tried it below.
SQL> create role session_role;
Role created.
SQL> grant select on v_$session to session_role;
Grant succeeded.
SQL> grant session_role to scott;
Grant succeeded.
SQL> connect scott/tiger;
Connected.
SQL> select count(1) from V$session;
COUNT(1)
———-
17
hi,
but i give this error!
SQL> select table_owner, table_name FROM dba_synonyms where synonym_name = ‘V$SESSION’;
TABLE_OWNER TABLE_NAME
—————————— ——————————
SYS V_$SESSION
SQL> GRANT SELECT ON V_$SESSION TO CONVERSION_USER;
GRANT SELECT ON V_$SESSION TO CONVERSION_USER
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
http://www.dba-oracle.com/sf_ora_00980_synonym_translation_is_no_longer_valid.htm
Check when you execute desc sys.v_$session or select * from sys.v_$session does that work?
The underlying object is a view, it could be something doesn’t exists or could be invalid.
SQL> select object_type, status from dba_objects where object_name = ‘V_$SESSION’;
OBJECT_TYPE STATUS
——————- ——-
VIEW VALID
CREATE TABLESPACE TBS_TEST DATAFILE ‘C:\oraclexe\app\oracle\oradata\XE\DTF_TEST.DBF’ SIZE 100M AUTOEXTEND ON;
CREATE USER USR_TEST IDENTIFIED BY MEDELLIN2013 DEFAULT TABLESPACE TBS_TEST;
GRANT CONNECT, RESOURCE TO USR_TEST;
SQL> CONNECT / AS SYSDBA;
Connected.
SQL> GRANT SELECT ON V_$SESSION TO USR_TEST;
Grant succeeded.
CREATE TABLE TEST(
ID NUMBER PRIMARY KEY,
NOM VARCHAR(100) NOT NULL);
CREATE TABLE AUDITORIA(
ID NUMBER PRIMARY KEY,
FECHA DATE,
CAMANDO VARCHAR(100),
IDTEST NUMBER,
NOMTEST VARCHAR(100),
USUARIO VARCHAR(100)
);
CREATE SEQUENCE SEQ_IDAUDITORIA;
INSERT INTO TEST VALUES(1,’111′);
INSERT INTO TEST VALUES(2,’222′);
INSERT INTO TEST VALUES(3,’333′);
INSERT INTO TEST VALUES(4,’444′);
INSERT INTO TEST VALUES(5,’555′);
create or replace
trigger TRG_AUDITORIA
BEFORE DELETE ON TEST
FOR EACH ROW
DECLARE
NOMUSUARIO VARCHAR(100);
NOMEQUIPO VARCHAR(100);
BEGIN
SELECT (SELECT distinct SYS.V_$SESSION.MACHINE FROM SYS.V_$SESSION WHERE ROWNUM=1) INTO NOMEQUIPO FROM DUAL;
SELECT USER INTO NOMUSUARIO FROM DUAL;
INSERT INTO AUDITORIA VALUES(SEQ_IDAUDITORIA.NEXTVAL,SYSDATE,’DELETE’,:OLD.ID,:OLD.NOM,NOMUSUARIO,NOMEQUIPO);
END;
Hi
my user having dba privilege
but when i give grants on v$session to another user it is giving error as follows
insuffiecient privileges
This article is really most lovely article and helpful article to give the session acess. These kind of questions are most commonly asked questions to dba. With this article you can also find out some most important complex sql interview questions https://www.complexsql.com/complex-sql-queries-examples-with-answers/ . I hope you like it.