[ORACLE DB] Create a log on trigger database to trace all session from user

Login Sys:
- Create Table to store name of trace file.

SQL> CREATE TABLE login_audit_info
(
logon_time DATE         ,
username   VARCHAR2(100),
tracefile  VARCHAR2(100)
);

- Create a logon trigger Database


CREATE OR REPLACE TRIGGER audit_login_trigger
   AFTER LOGON
   ON DATABASE
DECLARE
   l_user        SYS.dba_users.username%TYPE := USER;
   l_sql         VARCHAR2 (500);
   l_tracefile   VARCHAR2 (100);
   l_time        DATE;
BEGIN
   l_sql :=
         'alter session set events '
      || CHR (39)
      || '10046 trace name context forever, level 12'
      || CHR (39);
   l_time := SYSDATE;

   IF (l_user = 'GOLDLIVE')
   THEN
      EXECUTE IMMEDIATE l_sql;

      SELECT    pa.VALUE
             || '/'
             || LOWER (SYS_CONTEXT ('userenv', 'instance_name'))
             || '_ora_'
             || p.spid
             || '.trc'
        INTO l_tracefile
        FROM v$session s, v$process p, v$parameter pa
       WHERE     pa.NAME = 'user_dump_dest'
             AND s.paddr = p.addr
             AND s.audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');

      INSERT INTO login_audit_info (logon_time, username, tracefile)
           VALUES (l_time, l_user, l_tracefile);

      COMMIT;
   END IF;
END;
/

- When User login, will execute the trigger, Trace of session keep in login_audit_info.

SQL> select * from login_audit_info;

- To Disable Trigger.

SQL>alter trigger audit_login_trigger disable;