select on v$session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: select on v$session

  1. #1
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    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

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    Thanks Jim and Jurij!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width