select on v$session

[skid]

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

[jovery]

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

[jmodic]

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;

[skid]

Thanks Jim and Jurij!