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