Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Trigger to track connection in Oracle

Leave a reply

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;
/