How to create a logon trigger for session tracing

first create log table:

create table dba_user.sessiontrace(LDATE date, MACHINE  VARCHAR2(64), PROGRAM VARCHAR2(48), OSUSER  VARCHAR2(30), MODULE  VARCHAR2(64), SID NUMBER, SERIAL# NUMBER, SPID VARCHAR2(24));

grant necessary permissions to dba_user:

grant alter session to dba_user;

grant select on v_$session to dba_user;

grant select on v_$process to dba_user;

Create procedure to set session trace on:


CREATE OR REPLACE PROCEDURE dba_user.logon_actions IS
CURSOR user_prog IS
SELECT s.machine,s.program,s.osuser,s.module,s.sid, s.serial#,p.spid  FROM v$session s, v$process p where s.audsid=sys_context('USERENV','SESSIONID') and s.paddr=p.addr;
user_rec user_prog%ROWTYPE;
begin
OPEN user_prog;
FETCH user_prog INTO user_rec;
insert into sessiontrace values(sysdate, user_rec.machine,user_rec.program,user_rec.osuser,user_rec.module,user_rec.sid,user_rec.serial#, user_rec.spid);
commit;
execute immediate 'alter session set sql_trace=true';
CLOSE user_prog;
END;
/
 
Finally create login trigger to trace user:

CREATE OR REPLACE TRIGGER dba_user.ON_LOGIN_OF_DBUSER
AFTER LOGON ON DBUSER.SCHEMA
begin
dba_user.logon_actions;
end;
/