«

»

May 15

Using DBMS_MONITOR

— create logon trigger for one user;
CREATE OR REPLACE TRIGGER sde.logon_trace_trig AFTER LOGON ON sde.SCHEMA
BEGIN
dbms_session.set_identifier(‘SDE’);
END ;
/
show errors;

— create logon trigger for all users:
CREATE OR REPLACE TRIGGER logon_sess_id_trig AFTER LOGON ON database
BEGIN
dbms_session.set_identifier(substr(user, instr(user, ‘\’) + 1));
END ;
/
show errors;

— to use these, two options:
— If the trigger has set the CLIENT_ID
dbms_monitor.client_id_trace_enable(‘SDE’, TRUE, TRUE);
— then disable:
dbms_monitor.client_id_trace_disable(‘SDE’);
— Better way to get sid, serial# with tracefile name
select s.sid, s.serial#, s.username, s.module, a.value || ‘\’ || d.instance_name || ‘_ora_’ || p.spid || ‘.trc’ trace_file_path from v$parameter a, v$process p,v$session s, v$instance d
where s.paddr=p.addr and s.module = ‘ArcMap.exe’ and a.name = ‘user_dump_dest’;

— or, if you didn’t set an identifier, you can still trace with dbms_monitor:
select sid, serial# from v$session where username = ‘SDE’;
— Start tracing:
dbms_monitor.session_trace_enable(sid, serial#, TRUE, TRUE);
— then disable:
dbms_monitor.session_trace_disable(sid, serial#);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>