Author Archives: SandeepSingh

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Trigger to track connection in Oracle

This trigger is used to track the application when it connected and disconnected and record logintime when user login and what program he/she runs and what action performed.

Code:

			
CREATE TABLE con_audit (
  logintime TIMESTAMP DEFAULT SYSTIMESTAMP,
  username VARCHAR2(30),
  program VARCHAR2(64),
  machine VARCHAR2(64),
  session_id NUMBER,
  action VARCHAR2(10) 
);
-- Trigger to track connections 
CREATE OR REPLACE TRIGGER con_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO con_audit (username, program, machine, session_id, action)
  VALUES (USER, SYS_CONTEXT('USERENV', 'MODULE'),
          SYS_CONTEXT('USERENV', 'HOST'),
          SYS_CONTEXT('USERENV', 'SID'), 'CONNECT');
  COMMIT;
END;
/

		