-
I'm creating a procedure which select from v$session, I can select the v$session from sql*plus but seem can't access the v$session from a view.
How can I get away with this? Or any other way to access the v$session info. I don't know if this is the right way to do it, I'm planning to create a way in which would kill all the session of a specific user other than doing a 'alter system kill session...' repeatedly.
create or replace procedure session_Test
as
v_sid NUMBER;
begin
for rec in (select sid, serial#
from v$session
where username = user)
loop
null;
-- ... do something
end loop;
end;
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/16 PL/SQL: SQL Statement ignored
6/22 PLS-00201: identifier 'SYS.V_$SESSION' must be declared
-
If you are not creating this as user sys you have to grant select priveleges on v_$session directly to the user (not via a role)
Or if you intented to use this user as a general admin account you can grant select any dictionary (in 9i)
Regards
-
You as the owner of the procedure/view probably have been granted the access to V$SESSION through some role (probably through DBA role). To be able to access it from PL/SQL or from view, you need to have a direct grant to your schema. Use:
GRANT SELECT ANY TABLE TO skid;
or connect as SYS and do:
GRANT SELECT ON v_$session TO skid;
-
