rem 
rem SQL_TRACE - Example Code
rem

show parameter diagnostic_dest

rem
rem Session tracing
rem

alter session set tracefile_identifier='10046';

alter session set timed_statistics = true;

alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

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

-- Execute some queries or operations to be traced here --

select * from dual;

exit;

rem 
rem Tracing a process after it has started
rem 

SELECT p.pid,p.spid,s.sid
FROM v$process p,v$session s
WHERE s.paddr = p.addr
AND s.sid = &SESSION_ID;

column line format a79
set heading off
SELECT 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
  s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
FROM v$session s , v$process p
WHERE p.addr = s.paddr
AND s.username <> ' ';

rem 
rem don't forget to replace  with Oracle PID in next example
rem 

connect / as sysdba
oradebug setospid 
oradebug unlimit
oradebug event 10046 trace name context forever,level 12 

rem
rem Instance wide tracing
rem 

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

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

rem
rem Via a Logon Trigger
rem 

CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

-- Clean-up

DROP TRIGGER SYS.set_trace;