DanBrother的部落格

Enable SQL Trace For a Single Oracle DB User

-- Create Logon Trigger
create trigger t_scott_logon_trace after logon on scott.schema
begin
    execute immediate 'alter session set timed_statistics=true';
    dbms_session.set_identifier('TRACE_SCOTT');
end;
/

-- Enable Trace By Client_ID
exec dbms_monitor.client_id_trace_enable(client_id => 'TRACE_SCOTT', waits => TRUE, binds => TRUE);

-- Logon to Scott and Write a SQL statement
conn scott@TESTDB
select * from emp;
exit

-- Format SQL Trace Output Into Human Readable Format by TKProf utility
tkprof TESTDB_ora_29470.trc SCOTT_trace_report.txt

-- Diable Trace By Cleint_ID
exec dbms_monitor.client_id_trace_disable(client_id => 'TRACE_SCOTT');

-- Drop Logon Trigger
drop trigger t_scott_logon_trace;

 

[Reference]
http://portrix-systems.de/blog/brost/enable-sql-trace-for-a-single-user/
http://www.orafaq.com/wiki/TKProf