TRACE의 자동화

-- trace_on.sql
alter session set events '&1 trace name context forever, level &2';

-- trace_off.sql
alter session set events '&1 trace name context off';

-- trace_file.sql
-- trace_file_name
column trace_file_name new_value trace_file

select
  d.value||'/'||p.value||'_ora_'||s.spid||'.trc'
  as trace_file_name
from
  (
  select value
  from v$parameter
  where name = 'instance_name'
  ) p,
  (
  select value
  from v$parameter
  where name = 'user_dump_dest'
  ) d,
  (
  select spid
  from v$process
  where addr = (
    select paddr
    from v$session
    where sid = (select sid from v$mystat where rownum = 1)
    )
  ) s
;

prompt &trace_file

-- tkprof
host tkprof &trace_file &1