If you enable a trace, in this case using DBMS_MONITOR.SESSION_TRACE_ENABLE, can you count on the session's disconnect to disable tracing?
We're thinking about modifying a package which already executes upon Forms startup to include DBMS_MONITOR.SESSION_TRACE_ENABLE (we have to wait until we've executed DBMS_SESSION.SET_IDENTIFIER). The piece we're wondering about is whether or not we also have to update the database-level 'on logoff' trigger to run DBMS_MONITOR.SESSION_TRACE_DISABLE, or if the process of disconnecting a session automatically ends a tracing request.
The documentation is not very clear on wether or not it gets disabled upon session's termination and I dont have a 10g DB to test this with (gotta install one later here on HP tru64), but anyways, I would expect the same behaivour as the session's parameter SQL_TRACE, which in fact gets terminated one the session expire. If someone can ellaborate and make some test it will be very instructive since I would like to know as well.
I did try to find this out for myself using the view DBA_ENABLED_TRACES. It is supposed to display an entry for each trace enabled by DBMS_MONITOR. I consistently get no records from the view, even though I can verify that a trace file is being created. So I'm puzzled.
DBA_ENABLED_TRACES only seems to display records if you use DBMS_MONITOR to trace sessions by CLIENT_ID, MODULE, SERVICE_NAME & ACTION. If you trace by SID, then you get nothing.
I think this is because tracing enabled for the session ends when the session ends. Tracing enabled on any of CLIENT_ID, MODULE, SERVICE_NAME & ACTION is a persistent trace which kicks on whenever a session bears that property. Since it's purpose is to track connections in a connection pooling environment, it has to be a cross-connection feature that remains in effect even when a session ends.
I'm guessing based on some rudimentary testing, but I think I'm close.