Last January, in the following tweet, I pointed out that the documentation vaguely mentions that a trace file may be split into several files.

As a follow-up, few days later Jonathan Lewis published a post entitled Trace file size.

Until recently, I didn’t bother to investigate how that feature works. But, since I keep seeing more and more split trace files when I work for my customers, I wanted to know why and when the splits do happen.

After a number of observations, I inferred that the value to which MAX_DUMP_FILE_SIZE is set determines whether splits take place. Here’s an example:

  • Set the limit to 10 MB, activate SQL trace, and generate about 200 MB of trace data
SQL> alter session set max_dump_file_size = '10M';
SQL> alter session set tracefile_identifier = '10M';
SQL> execute dbms_session.session_trace_enable
SQL> @load.sql
  • Check the generated trace files(s): one single file having, as expected, a size of 10 MB was generated
$ ls -l *10M*.trc
-rw-r-----. 1 oracle oinstall 10485839 Oct 10 23:34 DBA121_ora_16253_10M.trc
  • Reconnect, set the limit to 100 MB, enable SQL trace, and generate about 200 MB of trace data
SQL> alter session set max_dump_file_size = '100M';
SQL> alter session set tracefile_identifier = '100M';
SQL> execute dbms_session.session_trace_enable
SQL> @load.sql
  • Check the generated trace files(s): as documented, five files (the first and the last four segments) having a maximum size of 1/5th of the limit (i.e. 20 MB) were generated; notice that because of the way trace files are generated, the aggregate size goes between 4/5th and 5/5th of the limit
$ ls -l *100M*.trc
-rw-r-----. 1 oracle oinstall 20971677 Oct 10 23:41 DBA121_ora_16357_100M_10.trc
-rw-r-----. 1 oracle oinstall 20971741 Oct 10 23:39 DBA121_ora_16357_100M_1.trc
-rw-r-----. 1 oracle oinstall 20971680 Oct 10 23:41 DBA121_ora_16357_100M_8.trc
-rw-r-----. 1 oracle oinstall 20971678 Oct 10 23:41 DBA121_ora_16357_100M_9.trc
-rw-r-----. 1 oracle oinstall  1074402 Oct 10 23:41 DBA121_ora_16357_100M.trc

Based on such observations, I run a series of tests with different values to find out what the threshold is. On the Oracle Linux database servers I checked (a VM running 12.1.0.2.160719, a physical machine running 12.1.0.1.0 and 12.1.0.2.0, a machine in the Oracle Cloud running 12.1.0.2.4, and the Oracle Database Exadata Express Cloud Service running 12.2.0.0.3), the threshold was always the value 51200 (with blocks of 512 bytes, that’s 25 MB). In other words, the splits occur only if MAX_DUMP_FILE_SIZE is set to 51200 or a greater value.

If you can test on a different plattform, please, leave a comment. I’m curious to know whether the limit is always 25 MB…

Finally, note that only when splits don’t occur a message like the following one isn’t written to the alert.log:

Non critical error ORA-48913 caught while writing to trace file "/u00/app/oracle/diag/rdbms/dba121/DBA121/trace/DBA121_ora_16253_10M.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...