Hi all,
I asked before about usage of dbms_monitor.session_trace_enable
And I got correct answer, doing it works fine.
But when I call dbms_monitor.serv_mod_act_trace_enable procedure, it doesn't generate *.trc file.
I checked dba_enabled_traces view, the trace is enabled,
but when I query v$session, sql_trace column of the session having specific module/action is "DISABLED".
Here is my work:
dev>
alter session set tracefile_identifier = 'WINSPLAY';
begin
dbms_application_info.set_module('dev', 'dev-act');
end;
/
SYS>
-- check sql_trace column value is "disabled"
select module, action, sql_trace from v$session where module = 'dev';
-- start sql trace
begin
dbms_monitor.serv_mod_act_trace_enable(
service\_name => 'orcl',
module\_name => 'dev',
action\_name => 'dev-act'
);
end;
-- check sql trace is on
select * from dba_enabled_traces;
-- check sql_trace column. - value is "DISABLED" , It must be "ENABLED" i guess.
select sql_trace from v$session where module = 'dev';
dev>
-- random query to be traced
select 'a' from dual connect by level <= 5;
/
SYS>
-- turn off SQL Trace
begin
dbms_monitor.serv_mod_act_trace_disable(
service\_name => 'orcl',
module\_name => 'dev',
action\_name => 'dev-act'
);
end;
/
-- get target session's trace file path and access
select p.tracefile from v$process p, v$sessions s
where p.addr = s.paddr and s.sid = #; -- # is dev's sid
With above commands, trc file must be in user dump dest.
But I can not find it.
Anything wrong ??
Thanks.
EDIT
service name:
select * from dba_enabled_traces;
-- when monitoring on
select sid, serial#, username, module, action, sql_trace from v$session where module = 'dev';
-- I captured this image while enabling SQL Trace
