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
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;
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 ;-)
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
what can i do for that? the tables under this schema does not exist.
Obviously it does exist in oracle server 7.3.3.