CREATE TEMPORARY TRACE EVENT SESSION statement

Creates a user trace event session.

Syntax

CREATE [ OR REPLACE ] TEMPORARY TRACE EVENT SESSION session-name 
[ ON SERVER ]
event-definition [ ,... ]
[ target-definition ]

event-definition :
ADD TRACE EVENT event-name [ ( WHERE search-condition ) ]

target-definition :
ADD TARGET FILE
  ( SET target-parameter-name=target-parameter-value [ ,... ] )

target-parameter-name :
{ filename_prefix 
 | max_size
 | num_files
 | flush_on_write
 | ["compressed"] }

Parameters

OR REPLACE clause
Specifying CREATE OR REPLACE creates a trace event session or replaces an existing trace event session with the same name.
ON SERVER clause
The trace event session records trace events from all databases on the database server. If this clause is not specified, then the trace event session only records trace events from the database on which the session is created.
WHERE clause
The WHERE clause allows an event to be traced conditionally based on its properties, and can contain expressions that refer to constants and event fields. If there are built-in functions used in search-condition, then they are evaluated on the connection generating the event. For example, using connection_property('number') = 101 logs only events for connections with the number 101.

search-condition is applied before events are sent to target-definition. If search-condition returns FALSE (or UNKNOWN), then the event is not sent to the target.

search-condition returns the error if it contains any of the following:
- Sub-queries
- User-defined functions
- Sequences
- Host variables
- Column references (may refer to fields of the event but not WHERE clause of the event)
- Connection or database variables
session-name
The name of the trace event session.
event-name
The name of the trace event to add to the session. System- and user-defined trace events are supported. Call the sp_trace_events system procedure to obtain a list of system-defined trace events.
target-name
The only supported value is FILE.

target-parameter-name

The following target parameters are supported:

`target-parameter-name`	`target-parameter-value`
filename_prefix	An ETD file name prefix with or without a path. ETD files have the extension .etd. This parameter is required.
max_size	The maximum size of the file in bytes. The default is 0, which means there is no limit on the file size and it grows as long as disk space is available. Once the specified size is reached, a new file is started.
num_files	Use this option to limit the number of files when max_size is set to a non-zero value. It is the number of additional files to preserve when event tracing information is split over many files. The default is 0, which means there is no limit on the number of files. When a file reaches its maximum size, the database server starts writing a new file. Each file has a file name suffix _N where N starts at 0. When num_files is not 0, older files are automatically removed. For example, if num_files is 2 and the current file number suffix is _100, then files with suffix _100, _99, and _98 are kept (the current file and 2 others). The file with suffix _97 is deleted. As each new file is written, this number suffix increases by 1.
flush_on_write	A boolean (true or false) value that controls whether disk buffers are flushed for each event that is logged. The default is false. When flushing is enabled, the performance of the database server may be reduced if many trace events are being logged.
[compressed]	A boolean (true or false) value that controls compression of the ETD file to conserve disk space. The default is false. Use brackets with this parameter name because it is a keyword in other contexts.

Remarks

Trace event sessions do not run until they are explicitly started with the ALTER TRACE EVENT SESSION statement. Trace event sessions capture trace events related to system behavior or for a particular user. Trace event sessions are stored in memory and are dropped when the database server stops if they have not been dropped explicitly.

System privileges

You must have the MANAGE ANY TRACE SESSION system privilege.

Side effects

None

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement creates an event tracing session that records information about the user-defined event my_event and the system-defined event SYS_ConsoleLog_Information to a file named my_trace_file:

CREATE TEMPORARY TRACE EVENT SESSION my_session
    ADD TRACE EVENT my_event, -- user event
    ADD TRACE EVENT SYS_ConsoleLog_Information -- system event
    ADD TARGET FILE (SET filename_prefix='my_trace_file', [compressed]=1); -- add a target

The following statement creates an event tracing session called MySession that records connection events for user DBA:

CREATE TEMPORARY TRACE EVENT SESSION MySession 

ADD TRACE EVENT SYS_RLL_Connect 

( WHERE user='DBA' );