-
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
-
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.
-
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
-
SQL> grant select on v_$session to system;
-
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
-
There is no synonym for it - you need:
select * from sys.v_$session
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|