If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SQL> GRANT SELECT ON V$SESSION TO user;
GRANT SELECT ON V$SESSION TO user
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL>

Where is my fault?

PS. i logged as SYSTEM AS SYSDBA

sys.v$session is a fixed view (a false table that is actually a C array or linked list in the kernel), so it is "special", it is not a regular object; e.g. you cannot drop, alter it, and you cannot grant anything on it, even if you are the owner.

But, sys.v_$session is a view "select * from sys.v$session", and you can grant anything on it (if you are the owner).
The v$session you access from the trigger is a public synonym for the view sys.v_$session.

So, you need to grant the select on the view:

grant select on v_$session to user;

while connected as SYS (the owner of the view)
and all works fine.

You could also try
grant select_catalog_role to user;

depending on
1) if you want your user to access other v$ views
2) if it works in triggers - never remember if roles are enabled or not in trigger ;-)

HTH
Al

PS In reality there's also gv$session involved in the picture, but essentially, it's the same as above.

Now only one trigger remains with some errors which inserts values into
TXSYS.DRQ_PENDING
what can i do for that? the tables under this schema does not exist.
Obviously it does exist in oracle server 7.3.3.