Access v$session in a trigger failed
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Access v$session in a trigger failed

  1. #1
    Join Date
    Jul 2006
    Posts
    3

    Access v$session in a trigger failed

    Hi, everyone

    I tried to crearte a trigger to monitor user log on event. In this trigger body I have a 'select ... from v$session' statement. When I create this trigger under system schema, I got a warning: Triggger created with compilation errors. With 'show errros', I saw 'table or view doesn't exist'. when replacing v$session with dual, the trigger can be created without problem. Creating the trigger under sys, there is no errors.

    So there must be somthing I'm missing about accessing v$session like views in the trigger body.

    Appreciate your clarifying my confusions.

    Denis

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    The problem is that you have access to v$session through a role and therefore cannot create any objects such as triggers, views or procedures which access it. Try logging on as SYS and giving a direct grant select to the user.

  3. #3
    Join Date
    Jul 2006
    Posts
    3
    Thanks for the quick reply. However, see the following, am I using grant correctly?

    SQL> connect /as sysdba
    Connected.
    SQL> grant select on v$session to system;
    grant select on v$session to system
    *
    ERROR at line 1:
    ORA-02030: can only select from fixed tables/views

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    SQL> grant select on v_$session to system;

  5. #5
    Join Date
    Jul 2006
    Posts
    3
    Why cann't I select from v_$session under system?

    SQL> connect / as sysdba
    Connected.
    SQL> grant select on v_$session to system;

    Grant succeeded.

    SQL> connect system/oracle
    Connected.
    SQL> select * from v_$session;
    select * from v_$session
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

  6. #6
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    There is no synonym for it - you need:

    select * from sys.v_$session

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no, just use v$session not select from sys.v_$session

    v$session is a synonym for sys.v_$session

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