18.104.22.168 - 64 bit on Solaris
It appears tracing has been enabled on our database in the last one week as the trace directory is getting filled up.
However this is what it's showing:
SQL> show parameter trace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace integer 0
sec_protocol_error_trace_action string TRACE
sql_trace boolean FALSE
trace_enabled boolean TRUE
It seems 11g have trace_enabled set to true by default.
My question is how do I find who's session is being traced?
Can I get the list of sessions that are being traced.
How do I switch the tracing off? I can say that trace _enabled has always been set to TRUE and the directory was not filling up until a week ago
ALTER SYSTEM SET SQL_TRACE=FALSE SCOPE=SPFILE;
# then restart the DB
TGG, the OP posted the database parameters related to trace and sql_trace clearly shows as FALSE.
gbite, I would suggest looking at the content of some of the trace files. We have experienced more than one platform version specific trace file generation issue which caused hundreds of useless trace files to be generated. If the files contain the same messages you can then search Oracle support for a known problem which might have a solution available. If all the traces just appear to be regular session sql traces then the suggestion to look to see if any database event logon triggers exist and turn trace on.
Removing the trace files at the OS level should be an option in the meantime.
HTH -- Mark D Powell --
My colleague said during some reported performance issue last week, he thinks using Grid control 11g he enabled tracing for a session he thinks was responsible for high % of database activity and I reckon that the trace files started been written then. Since this was probably enabled through greed control and it was last week, how can I get the user for which tracing was enabled?
Since this is likely to be session tracing, should restarting the database stop the tracing?
select sql_trace from v$session;
it's DISABLED by default - if it's been chanegd you can find it that way.
Otherwise the trace file should have the unix pid in it - you can link this back to v$session by joining to v$process.
select sid from v$session where paddr = (select addr from v$process where spid=pid from trace file)
Thanks to everyone for your inputs.
This is solved now by disabling tracing from the Top Consumer tab of Grid Control (GC) 11g as this was where it appears to be enabled in the first place.
It was enabled for top module which was the main application module and it was writing multiple trace files every minute.
This would have been very difficult to solve outside GC from where it was enabled and it is just a click to accomplish on GC.