How can I relate an Oracle Unix process to an oracle connection?

From: Dick Westeneng <dwesteneng_at_baan.nl>
Date: 1998/02/18
Message-ID: <01bd3c44$cf71db30$9406010a_at_cnl03085>#1/1

Hi,

Got some problems with my first posting. Can not open the newsgroup again. If you have a solution to next question please provide me the answer also me by email.

Currently we are tracing with a lot of people together who can logon for a very short time and then logoff again. This makes it hard to see with user created with Oracle trace file.
To trace with Oracle connection was started by with UNIX pid I wrote the following structure:

audit connect;

create table user_proc_pid (

    pid number,
    sessionid number
);

insert into user_proc_pid

    select pid, 0 from v$process;

create table user_process (

    username varchar2(15),

    pid         number,
    logon       date,
    logoff      date

);

create or replace trigger user_process_update before insert on sys.aud$
for each row
declare

    pid integer
begin

    if (new.action_name = "LOGON") then

        select pid into pid
        from   v$process
        where  userid = :new.username
        and    pid not exist (
                select pid
                from   user_proc_cur);
        insert into user_proc_pid (pid, sessionid)
                values (:pid, :new.sessionid);
        insert into user_process (username, pid, logon)
                values (:new.username, :pid, SYSDATE);

    end if;
    if (new.action_name = "LOGOFF") then

        delete  from  user_proc_pid
                where sessionid = :new.session.id;
        update  user_process
                set logoff = SYSDATE
                where username  = :new.username
                and   sessionid = :new.sessionid
                and   logon < SYSDATE

    end if;
end
/

But running this script gave the problem: create or replace trigger user_process_update

                          *

ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS

$ oerr ora 4089
04089, 00000, "cannot create triggers on objects owned by SYS" // *Cause: An attempt was made to create a trigger on an object owned by SYS.
// *Action: Do not create triggers on objects owned by SYS.

This error
Can anyone help me to create a trigger or something like that to see with users connects with pid?

Kind regards,

Dick Westeneng
BBO Baan Business Optimizations
Office: +31 3 42 42 73 88
Email: dwesteneng_at_baan.nl Received on Wed Feb 18 1998 - 00:00:00 CET