rem -----------------------------------------------------------------------
rem Filename:  logintrig.sql
rem Purpose:   Audit user logins and enable SQL tracing for selected users
rem Date:      09-Jul-2005
rem Author:    Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CONNECT / AS SYSDBA

DROP TABLE user_login_audit;

CREATE TABLE user_login_audit (
	login_time	DATE,
	username        VARCHAR2(30),
	machine		VARCHAR2(30),
        command		VARCHAR2(128)
);

CREATE OR REPLACE TRIGGER user_login_trig
	AFTER LOGON ON scott.SCHEMA
DECLARE
  v_username sys.v_$session.username%TYPE;
  v_machine  sys.v_$session.machine%TYPE;
  v_command  sys.v_$session.command%TYPE;
BEGIN
  SELECT username, machine, command 
    INTO v_username, v_machine, v_command
    FROM sys.v_$session 
   WHERE audsid = USERENV('SESSIONID')
     AND audsid != 0  -- Don't Check SYS Connections
     AND rownum = 1;  -- Parallel processes will have the same AUDSID's

  INSERT INTO sys.user_login_audit 
	VALUES (SYSDATE, v_username, v_machine, v_command);

  IF UPPER(v_machine) like '%PC1%' THEN		-- Start SQL trace for users from PC1
     dbms_session.set_sql_trace(TRUE);
  END IF;
END;
/
SHOW ERRORS

CONNECT SCOTT/TIGER

CONNECT / AS SYSDBA

SELECT * FROM sys.user_login_audit;