Name

trace_on — Enable extra debug logging

Synopsis

integer trace_on ( in parameter varchar );
 

Description

This function requires dba privileges.

This function enables logging specified server operations for debugging purposes. The log entries will be shown at the server console (if started with foreground option) and will be written into the server message log file. The traceable events are divided into several groups: user activity, transactions, compilation of the SQL statements, DDL statements, statements execution and VDB actions.

Parameters

The following options are available for logging:

user_names - include the full user name, otherwise user ID will be logged.
user_log - log the connects/disconnects for users.
failed_log - log incorrect logins.
compile - log the names of procedures / triggers name compiled.
ddl_log - log the DDL statements execution.
client_sql - log the compilation of the client's SQL statements (first 500 chars).
errors - log all server errors.
dsn - log the connection/disconnection to DSNs, registration and removal of the DSNs, compilation of the SQL statements executed thru the VDB.
sql_send - log the compilation of SQL statements executed thru the VDB.
transact - log the transactions.
remote_transact - log the remote transactions.
exec - log SQL statement execution.
soap - log SOAP server requests and responses.
thread - log THRD_1 %ld OS threads freed. This is when OS threads are freed due to inactivity (being idle for more than ThreadCleanupInterval time).
cursor - log CURS_[0-9] - various VDB statements actions.

Return Types

Upon success zero will be returned, otherwise an error is signalled.

The message log file and/or server debug screen will list details for activated log options. The formats are as follows:

USER_0 (user) (IP) (peer) logout
USER_1 (user) (IP) (peer) login
FAIL_0 (user) (IP) (peer)
COMP_0 (user) (IP) (peer) trigger (name)
COMP_1 (user) (IP) (peer) procedure (name)
DDLC_0 (user) (IP) (peer) Create table (name)
DDLC_1 (user) (IP) (peer) Drop table (name)
DDLC_2 (user) (IP) (peer) Create procedure (name)
DDLC_3 (user) Drop procedure (name)
DDLC_4 (user) Create view (name)
DDLC_5 (user) Create index (name) or (table name)
DDLC_6 (user) Drop index (name) or (table name)
DDLC_7 (user) Rename table (new name) or (old name)
DDLC_8 (user) Create trigger (name) or (table name)
DDLC_9 (user) drop trigger (name) or (table name)
CSLQ_0 (user) (IP) (peer) (sql)
ERRS_0 (code) (server code) (error text)
DSNL_0 (dsn) (sql)
DSNL_1 Disconnecting DSN (name)
DSNL_2 (user) (IP) Registration remote data source (name)
DSNL_3 (user) Disconnect remote data source (name)
DSNS_0 (dsn) (sql) (prepare)
DSNS_1 (dsn) (sql) (execute)
LTRS_0 (user) (from) (peer) Begin transact tnx
LTRS_1 (user) (from) (peer) (Commit / Rollback) transact txn
LTRS_2 (user) (from) (peer) Restart transact txn
RTRS_0 (user) (from) (peer) (dsn) Begin transact tnx autocommit: (on/off)
RTRS_1 (user) (from) (peer) (dsn) (Commit / Rollback) transact tnx autocommit: (on/off)
EXEC_0 (user) (from) (peer) Exec cursor (stmt)
EXEC_1 (user) (from) (peer) (stmt) Exec (n) time(s)
SOAP_0 (request)
SOAP_1 (response)

Errors

Table 24.83. Errors signalled by trace_on

SQLState Error Code Error Text
22005 SR322 "option" is not valid trace_on option

Examples

Example 24.422. Simple example

To show users logging in to the server and failed user logins

SQL> trace_on ('user_log', 'failed_log');

Done. -- 0 msec.
SQL>

The server console  and log file may thus contain lines such as:
...
17:17:24 Server online at 1111 (pid 2173)
17:17:36 USER_0 0 xxx.xx.xx.xxx 1111:2 logout
17:17:40 USER_1 0 xxx.xx.xx.xxx 1111:3 login
17:17:45 USER_0 0 xxx.xx.xx.xxx 1111:3 logout
17:18:04 FAIL_0 dba xxx.xx.xx.xxx
...