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
Table24.84.Errors signalled by
trace_on
| SQLState | Error Code | Error Text |
|---|---|---|
| 22005 | SR322 | "option" is not valid trace_on option |
Examples
Example24.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
...