玉面飞龙的BLOG

使用sql trace的时候，往往会产生很大的trace file。但并不是该文件的所有内容我们都感兴趣。往往在我们跟踪错误的时候，只注意查找最后出错的SQL trace内容，这时候我们可以enable PGA buffer trace。它可以避免产生很大的trace文件。

Circular Trace Buffers

The following events have been tested successfully in 7.3.4 and were still working in Oracle 9.2.

In Oracle 8.1.6 and above, the default value for the MAX_DUMP_FILE_SIZE is UNLIMITED. In Oracle 8.1.5 and below the default value is 512 database blocks. In either case it can be altered dynamically; however high volume trace may still rapidly fill up a disk.

If only the last few lines of the trace file are required, then a circular trace buffer can be used

Enabling the Trace Buffer

To enable a circular trace buffer use

    ALTER SESSION SET EVENTS
'immediate trace name trace_buffer_on level level‘;

where level is the size of the buffer in bytes e.g.

    ALTER SESSION SET EVENTS
'immediate trace name trace_buffer_on level 65536';

creates a 65536 byte circular trace buffer

Note that the following ORADEBUG command is equivalent

    ORADEBUG DUMP TRACE_BUFFER_ON level

Dumping the Trace Buffer

The buffer is dumped to the trace file when the ksedmp function is called which is the case when an ORA-600 is signalled

The buffer can be dumped on demand using the command

    ALTER SESSION SET EVENTS

'immediate trace name trace_buffer_off';

or alternatively

    ORADEBUG DUMP TRACE_BUFFER_OFF 0

Note that a message is included in the trace file if trace has been discared from the trace buffer. For example

    Trace output buffer of 65536 bytes wrapped 8 times

Also the following message is written to the end of the trace file when it becomes full

    *** Trace file full ***