月度存档: 七月 2009

how to fix “trace file full” in a trace file

User wants to trace a session , so he starts a sesson and I open a trace for him. but he says, he finds
*** Trace file full ***
in the end of the trace file.

I check the file size of the trace, it’s 5120KB, so i believe there is a parameter in oracle database which limits the trace file size. from the metalink, i get the parameter — MAX_DUMP_FILE_SIZE.

You can use below command to find the value of this parameter,

SHOW PARAMETER MAX_DUMP_FILE_SIZE

–here is the output
NAME                                 TYPE        VALUE
———————————— ———– ———-
max_dump_file_size                   string      10240

so I need to increase the size to a larger value.
First, i use below command to increase the value,
Alter SESSION SET MAX_DUMP_FILE_SIZE =102400

but it doesn’t work, the trace file doesn’t grow up once it’s 5120KB.
Since I use

exec dbms_system.set_sql_trace_in_session(28,18792,FALSE);

to catch trace file, I think the trace file may be generated by another session, so I change the parameter by

Alter SYSTEM SET MAX_DUMP_FILE_SIZE =102400;

and this time , it does work ! the trace file is 14.7MB when the session finishes..

So , here is the summary,

the values of MAX_DUMP_FILE_SIZE in database uses the default unit which is OS blocks, so for example, if your logical file system block size is 512 bytes and you do not want to exceed 5 Mg for the trace file size, you would set the MAX_DUMP_FILE_SIZE to 10000. And if you want to have larger trace file, you would set the MAX_DUMP_FILE_SIZE to a larger value , for example, 102400.