对特定session进行跟踪

accept p_osuser prompt 'OSUSER_NAME? :'

set feedback off
-- SQLトレース実行文
ALTER SESSION SET TIMED_STATISTICS=TRUE         -- 時間情報の取得
/
ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED  -- ファイルサイズ制限解除
/
set feedback on

-- 同じマシンUSERのTRACE取得
col USERNAME for a15
col MACHINE  for a25
col PROGRAM  for a25

prompt exit  trace   : ALTER SYSTEM SET TIMED_STATISTICS=TRUE ;;
prompt start trace   : ALTER SYSTEM SET TIMED_STATISTICS=FALSE;;

select     'EXECUTE DBMS_SYSTEM.SET_EV                  ('||vs.SID||','||vs.SERIAL#||',10046,8,'''');'
||chr(10)||'EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||vs.SID||','||vs.SERIAL#||',TRUE );'
||chr(10)||'EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION('||vs.SID||','||vs.SERIAL#||',FALSE);'
||chr(10)||'EXECUTE DBMS_SYSTEM.SET_EV                  ('||vs.SID||','||vs.SERIAL#||',10046, 0,'''');'
||chr(10)||'trace:'
         ||(select vp.spid
             from v$process vp
            where vp.addr = vs.paddr
            ) 
         ||' ,schema:'   ||vs.schemaname
         ||' ,osuser:'   ||vs.osuser
         ||' ,program:'  ||vs.program
||chr(10)||'trace file:'
         ||(
            select
              d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
            from
              ( select p.spid
                from v$process p
                where p.addr = vs.paddr) p,
              ( select t.instance from v$thread  t,v$parameter  v
                where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
              ( select value from v$parameter where name = 'user_dump_dest') d
            )
  from v$session   vs
 where vs.USERNAME       is not null
   and upper(vs.OSUSER)  like upper('%&p_osuser.%')
   and vs.AUDSID         <> userenv('sessionid')
/

prompt tkprof mydb_ora_xxxx.trc mydb_ora_xxxx.txt explain=xxxx/xxxx@mydb sort=exeela fchela prsela table=sch.plan_mydb_ora aggregate=yes sys=no waits=yes

undefine p_machine