CODEWITHSUNDEEP
sqloracle-databaseoracle10gtrace
Chaitanya Munipalle
Chaitanya Munipalle

Reputation: 724

How to use single trace file for all the sessions in Oracle?

In Oracle, trace is working fine when I am altering session parameters and set tracefile_identifier as shown below. 

alter session set max_dump_file_size = unlimited;
alter session set timed_statistics=true;
alter session set statistics_level=ALL;
alter session set tracefile_identifier=mytracefile;
alter session set sql_trace=true;
alter session set events '10046 trace name context forever, level 12';

But I want to generate single trace file for all the connections in the database for a specific period of time. I tried the same commands but by altering the system instead of session. I am not able to alter tracefile_identifier. I am getting ORA-02096: specified initialization parameter is not modifiable with this. Please let me know how to achieve this.

Upvotes: 2

Views: 2701

Answers (2)

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

The trcsess tool is really cool and maybe what you are after. I use it to trace applications utilizing a connection pool - you don't know which session will be active.

Oracle 10g introduced the trcsess utility, which allows trace information from multiple trace files to be identified and consolidated into a single trace file.

trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>]
        [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>

output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' supported.

Upvotes: 2

Mark J. Bobak
Mark J. Bobak

Reputation: 14403

In short, it's not possible.

Oracle trace files are written on a per process basis. So, any background process (PMON,SMON,etc) will write it's own trace file, as will any user's server process.

If you run shared server, you'll get multiple sessions' trace output in the same file, as those sessions share a specific server process.

But, to get everything to write to a single trace file is not possible.

If you want to add more info to the question, in terms of the business requirement, and what it is you're actually trying to achieve, I may be able to offer a better answer or alternate solution.

Upvotes: 2

Related Questions