Tuesday, November 06, 2007

Trace a user

create or replace TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user_identifier varchar2(64);
BEGIN
SELECT SYS_CONTEXT('USERENV', 'OS_USER') ':'
SYS_CONTEXT('USERENV', 'IP_ADDRESS')
INTO v_user_identifier
FROM dual;
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;
/

select distinct CLIENT_ID from v$active_session_history where CLIENT_ID is not null;

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(,
waits => TRUE, binds => FALSE);

select * from DBA_ENABLED_TRACES

tkprof tracefile outputfile WAITS=no SYS=no EXPLAIN=no RECORD=filename insert=filename

No comments: