Search code examples
databaseoracle-databaseoracle12c

Oracle v$session,v$mystat gives error: table or view does not exist on select

I have this select statement in a procedure inside a package

select distinct sid into a from v$mystat;

SELECT PADDR,TERMINAL,SID,SERIAL# INTO CURSES,CURTER,CURSID,CURSER from v$session where sid=a;

These two statements returns the error

PL/SQL: SQL Statement ignored, PL/SQL: ORA-00942: table or view does not exist

This error is occurring inside procedure but I can run these statements individually from sqlplus or in an sql worksheet in the same user.

I tried to give privilege using grant keyword but gave an error below. Do you have any possible solution?

enter image description here

Oracle version: 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Solution

  • You should grant select on v_$session which is the object behind v$session synonym.

    Same with V_$MYSTAT object for v$mystat synonym.

    GRANT SELECT ON V_$MYSTAT TO <user>

GRANT SELECT ON v_$session TO <user>