Re: Problem with logon trigger

On 14 Jan 2002 01:22:56 -0800, georg.scholz_at_ubisol.at (Georg Scholz) wrote:

>Hello,
>
>we are using Oracle 8.1.7 on a W2K server.
>
>I have a problem with a logon trigger.
>The idea is to store the OS-Username in a separate table when a user
>logs on:
>
>CREATE OR REPLACE TRIGGER DB_LOGON
>after logon on database
>begin
> INSERT INTO T_SESSIONS (SESSIONID, TERMINAL, OSUSER, LOGONTIME,
>LASTALIVE)
> SELECT userenv('sessionid'), TERMINAL, OSUSER, sysdate as s1, sysdate
>as s2
> FROM SYS.V_$SESSION WHERE AUDSID = userenv('sessionid');
>end;
>
>Problem: when creating this trigger, I get: "SYS.V_$SESSION must be
>declared".
>
>However, in a normal SQL PLUS Window, the SQL-statement contained in
>the trigger works fully OK!! I tried to create a synonym to
>SYS.V_$SESSION, but I get the same error.
>
>Do you have any hints??
>
>Thanks in advance
>Georg Scholz

You seem to try to reinvent the wheel. Oracle has already the audit function for this. Just set your audit_trail parameter to db, issue audit connect and you're there.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jan 14 2002 - 12:39:11 CST