Friday, October 5, 2018

ORA-48913: Writing into trace file failed, file size limit...


In some environments DBAs limit the size of trace files generated by the database. This included all trace files that could get generated under USER_DUMP_DEST/DIAGNOSTIC_DEST). The parameter to set the limit for trace files is MAX_DUMP_FILE_SIZE and its value is in OS number of blocks. After setting this value, if any trace file size would increase form the size specified in this parameter, ORA-48913 would be recorded in alert log file.

Suppose if you want to set the trace files size limit to 2MB, and your OS block size is 512 bytes (in NTFS file system), you will specify value of MAX_DUMPFILE_SIZE to 4096 (4096x512 => 2097152 => 2 MB). If any trace file would exceed in size beyond 2MB, would not have any new entries in it because size limit would have already reached and ORA-48913 would be reported in alert log file along with trace file name that could not be further extended. Error in alert log is similar to the following
Non critical error ORA-48913 caught while writing to trace file "D:\ORACLE\diag\rdbms\mydb\mydb\trace\mydb_lms0_305660.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [2097152] reached
Writing to the above trace file is disabled for now on...

You would need to reset value of MAX_DUM_FILE_SIZE in order to have your trace files size extend beyond 2 MB now. You can use “alert system” command to change the value. You may also set it to UNLIMITED.
SQL> ALTER SYSTEM SET max_dump_file_size=unlimited;

Alternatively, value of this parameter could also be set at session level. This is useful when you plan to generate some SQL trace files and you to apply dump file size limit on only SQL trace files being generated by the current session. To do this, use ALTER SESSION command to set value of this parameter to UNLIMITED, or any other required value. ALTER SESSION command can also be used from within PL/SQL code using EXECUTE IMMEDIATE.
SQL> ALTER SESSION SET max_dump_file_size=unlimited;

No comments:

Post a Comment

Popular Posts - All Times