Knowledge Base : Trace a session

27 Juni 16, 2022

Trace a user’s session in Oracle Database

Set a logon trigger

create or replace trigger set_trace after logon on database
begin
if user not in (‚SYS‘,’SYSTEM‘) then
if user in (‚SCOTT‘) then
execute immediate ‚alter session set timed_statistics=true‘;
execute immediate ‚alter session set max_dump_file_size=unlimited‘;
execute immediate ‚alter session set sql_trace=true‘;
end if;
end if;
exception
when others then
null;
end;

Trace other people’s session using DBMS_SYSTEM

SQL> exec dbms_system.set_bool_param_in_session(10,20,’timed_statistics‘,true);
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_int_param_in_session(10,20,’max_dump_file_size‘,2147483647);
PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(10,20,true);
PL/SQL procedure successfully completed.
SQL> exec dbms_system.set_sql_trace_in_session(10,20,false);
PL/SQL procedure successfully completed.

Trace Levels

  • Level 0 = No statistics generated
  • Level 1 = standard trace output including parsing, executes and fetches plus more.
  • Level 4 = Same as level 1 but includes bind information
  • Level 8 = Same as level 1 but includes wait’s information
  • Level 12 = Same as level 1 but includes binds and waits

For a complete list of events that can be set see file $ORACLE_HOME/rdmbs/mesg/oraus.msg.

See also: How to set Trace (Pete Finnigan)