使用SYS用户进行创建：
SYS@dbabd> create or replace trigger scott_login_trace 2 after logon on database 3 begin 4 if user = 'SCOTT' 5 then 6 execute immediate 'alter session set tracefile_identifier = SCOTT'; 7 dbms_session.session_trace_enable(waits => true, 8 binds => true, 9 plan_stat => 'all_executions' 10 ); 11 end if; 12 end; 13 / Trigger created.查看触发器状态：
SYS@dbabd> select owner,trigger_name,status,trigger_body from all_triggers where owner = 'SYS' and trigger_name = 'SCOTT_LOGIN_TRACE'; OWNER TRIGGER_NAME STATUS TRIGGER_BODY ---------- -------------------- ---------- -------------------------------------------------------------------------------- SYS SCOTT_LOGIN_TRACE ENABLED begin if user = 'SCOTT' then execute immediate 'alter session set tracefile_identifier = SCOTT'; dbms_session.session_trace_enable(waits => true, binds => true, plan_stat => 'all_executions'); end if; end;创建完触发器之后，SCOTT用户只要一登录就会默认开启跟踪。
总结以上梳理了常见的开启SQL TRACE的几种方式，但是原始的trace文件可读性比较差，通常不会直接去读取，而是通过工具tkprof进行格式化之后进行阅读，关于tkprof的使用可以参考我另一篇博文：Oracle 11g R2性能优化 tkprof