rem procedure to trace all sessions at a given point of time rem rem Biju Thomas REM set serveroutput on verify off declare cursor csess is select s.osuser pu, s.username su, s.status stat, s.sid ssid, s.serial# sser, lpad(p.spid,7) spid, s.program txt from v$process p, v$session s where p.addr=s.paddr and s.username like upper('&1'); begin dbms_output.put_line ('The Following Sessions are Traced'); dbms_output.put_line ('================================='); dbms_output.put_line ('OS USER ORA USER STATUS SID SER# UNIX ID PROGRAM NAME'); dbms_output.put_line ('-------- -------- -------- ------- ------- ------- ----------------------------'); for rsess in csess loop sys.dbms_system.set_sql_trace_in_session(rsess.ssid, rsess.sser, true); dbms_output.put_line (rpad(rsess.pu,9) || rpad(rsess.su,9) || rpad(rsess.stat,9) || rpad(rsess.ssid,8) || rpad(rsess.sser,8) || rpad(rsess.spid,8) || rsess.txt) ; end loop; end; /