v$session in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: v$session in procedure

  1. #1
    Join Date
    Nov 2000
    Posts
    245

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to grant select privs on v$session directly to user

    connect as sys

    grant select on v_$session to user


  4. #4
    Join Date
    Feb 2001
    Location
    Adelaide, Australia
    Posts
    159
    surely any user with resource role should be able to see v$session?


  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Permissions from roles can not be seen in a pl/sql procedure.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Posts
    245

    thanks, after grant select it works


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