-
Does anyone tell me How to cursor with v$session in a procedure?
I try
CURSOR CR_SESSION IS
select sid, serial# from sys.v$session where type='USER';
or
CURSOR CR_SESSION IS
select sid, serial# from sys.v_$session where type='USER';
or
CURSOR CR_SESSION IS
select sid, serial# from v$session where type='USER';
all get error:
PLS-00201: identifier 'SYS.V$SESSION' must be declared
thanks
-
Have you granted permission explictly to your user?
Code:
SQL> declare
2 cnt number;
3 begin
4 select count(*) into cnt from v$session;
5 end;
6 /
select count(*) into cnt from v$session;
*
ERROR at line 4:
ORA-06550: line 4, column 31:
PLS-00201: identifier 'SYS.V_$SESSION' must be declared
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored
SQL> connect sys
Enter password: **********
Connected.
SQL> grant select on v_$session to jeffh;
Grant succeeded.
SQL> connect jeffh/jeffh
Connected.
SQL> declare
2 cnt number;
3 begin
4 select count(*) into cnt from v$session;
5 end;
6 /
PL/SQL procedure successfully completed.
-
you have to grant select privs on v$session directly to user
connect as sys
grant select on v_$session to user
-
surely any user with resource role should be able to see v$session?
-
Permissions from roles can not be seen in a pl/sql procedure.
-
thanks, after grant select it works