Re: SQL trace for connections coming in for a database service
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: Brandon.Allen@xxxxxxxxxxx
- Date: Fri, 17 Jul 2009 13:39:38 -0500
Brandon,
Not true. Here's a simple script that can be tested with sqlplus.
select count(*) from v$session where module = 'mod1' and action = 'act1';
exec dbms_application_info.set_module('mod1', 'act1')
select count(*) from v$session where module = 'mod1' and action = 'act1';
select '1' from dual;
exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$USERS',
module_name=>'mod1', action_name=>'act1')
select '2' from dual;
1. Execute the script.
2. Your trace file will include the bottom two statements.
3. Exit sqlplus.
4. Comment out the serv_mod_act_trace_enable call.
5. Execute the script again (this time, it doesn't activate its own
tracing).
6. Your trace file will include everything that happens after the
set_module call, based on the standing order of the ..._trace_enable call
executed from the prior (now exited) session.
(Thank you Jeff Holt, for the test.)
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
http://twitter.com/cary_millsap
On Fri, Jul 17, 2009 at 10:51 AM, Allen, Brandon
<Brandon.Allen@xxxxxxxxxxx>wrote:
> Good suggestion, but if I understand correctly, the
> serv_mod_act_trace_enable procedure will only trace any existing sessions
> for the specified service/module/action at the time the procedure is
> executed. If you want to automatically trace all future sessions for that
> service name as well, I think you might need to create a logon trigger like
> this:
>
> CREATE OR REPLACE TRIGGER trace_service
> AFTER LOGON ON DATABASE
> BEGIN
> IF SYS_CONTEXT('userenv','service_name') = 'ssi' THEN
> dbms_monitor.session_trace_enable(waits=>true,binds=>true);
> END IF;
> END;
> /
>
> Regards,
> Brandon
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of D'Hooge Freek
>
> exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'service_name',
> waits => TRUE, binds => true);
>
>
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
