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.
The following options are available for logging:
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)
SQLState | Error Code | Error Text |
---|---|---|
22005 | SR322 | "option" is not valid trace_on option |
To show users loging 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 ...