Active Sql Tracing

Activate sql tracing on current session

— set maximum trace file size

alter session set max_dump_file_size='4000m';

— set a prefix to the trace file, by default it’s called <dbname>_ora_<spid>.trc

alter session set tracefile_identifier ='perf_test';

— activate basic sql_trace

alter session set events '10046 trace name context forever, level 1';

— activate basic sql_trace + bind variables

alter session set events '10046 trace name context forever, level 4';

— activate basic sql_trace + wait events

alter session set events '10046 trace name context forever, level 8';

— activate basic sql_trace + bind variables + wait events

alter session set events '10046 trace name context forever, level 12';

— diactivate sql tracing

alter session set events '10046 trace name context off';

Activate sql tracing on other sessions

— to identify other session’s sid and serial#

set lines 200 pages 1000 verify off
col sid for 99999
col serial# for 99999
col username for a10
col process for a10
col PROGRAM for a30
col MODULE  for a30
col logon_time for a20
col machine for a20
col status for a10

select sid,
       serial#,
       username,
       status,
       process,
       PROGRAM,
       machine,
       MODULE,
       to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss') logontime
  from v$session
 where username like upper('%&&username%')
   and STATUS like upper('%&&status%')
order by logontime;

Activate sql tracing on user by logon trigger

— to activate sql_tracing on user, say ‘scott’ in this example

create or replace trigger active_sql_trace_on_user
after logon on database
declare
v_user dba_users.username%TYPE:=user;
sql_stmt1 varchar2(256) :='alter session set events '||chr(39)||'10046 trace name context forever, level 12'||chr(39);
begin
if (v_user='SCOTT') THEN
execute immediate sql_stmt1;
end if;
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: