oracle jdbc debug

To get Oracle JDBC log output, you must use the oracle driver debug JAR files (in your classpath), which are indicated with a "_g" in the file name, ex. ojdbc5_g.jar.

Then you need to enable java.util.logging (since Oracle 10/JDK1.4)to see the activities

java -Doracle.jdbc.Trace=true  -Djava.util.logging.config.file=c:/myapp/oracledebug.properties  MyClass

The oracledebug.properties contains the logging configuration.

handlers = java.util.logging.FileHandler
#   or can be handlers = java.util.logging.ConsoleHandler
java.util.logging.FileHandler.pattern = c:/myapp/jdbc.log
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
#   or can be java.util.logging.FileHandler.formatter = java.util.logging.XmlFormatter

oracle.jdbc.driver.level = FINE

# Uncomment and/or change the levels for more detail
#oracle.jdbc.connector.level = FINE
#oracle.jdbc.internal.level = FINE
#oracle.jdbc.datum.level =  FINEST
#oracle.jdbc.adt.level = FINEST
#oracle.jdbc.conversion.level = FINEST
#oracle.jdbc.adt.level = FINEST
#oracle.jdbc.thin.level = FINEST
#oracle.jdbc.datum.level = FINEST
#oracle.jdbc.kprb.level = FINEST
#oracle.jdbc.pool.level = FINEST
#oracle.jdbc.xa.level = FINEST
#oracle.jdbc.sqlj.level = FINEST
#oracle.jdbc.oci.level = FINEST
#oracle.jdbc.jpub.level = FINEST


# Category definitions (and corresponding levels) are listed in the following:
#
#   USER_OPER     JDBC API level tracing.
#   INFO          (expected volume: low)
#
#   PROG_ERR      Program error (eg. an unexpected value encountered
#   SEVERE        in a switch statement).  Messages of this category
#                 is not maskable.
#                 (expected volume: low)
#
#   ERROR         Error conditions that usually lead to catastrophic
#   SEVERE        or unrecoverable results.  Messages of this
#                 category is not maskable.
#                 (expected volume: low)
#
#   WARNING       Error conditions that are usually recoverable.
#   WARNING       Note that the module and sub-module filters do not
#                 apply to messages of this category.  See also
#                 enableWarning().
#                 (expected volume: low)
#
#   FUNCTION      Function entry/return information.
#   FINE          (expected volume: medium)
#
#   DEBUG1        High-level debug information.
#   FINER         (expected volume: medium)
#
#   DEBUG2        Detail debug information.
#   FINEST        (expected volume: high)
#
#   SQL_STR       SQL string
#   CONFIG        (expected volume: low)

For OCI connection, it's a little bit different.

If you use the OCI driver, you can enable Net8 tracing on both the client and the server. Keep in mind, however, that enabling Net8 tracing translates into a substantial performance hit.

To enable client tracing, you add four parameters to your sqlnet.ora file, which is located in your $ORACLE_HOME\network\admin directory.

TRACE_LEVEL_CLIENT = SUPPORT
TRACE_DIRECTORY_CLIENT = c:\oracle\trace
TRACE_UNIQUE_CLIENT = ON
TRACE_FILE_CLIENT = SESS

TRACE_LEVEL_CLIENT can be one of the following four values:

0 or OFF
4 or USER
10 or ADMIN
16 or SUPPORT

To turn tracing on and get the most amount of information, specify the following:

TRACE_LEVEL_CLIENT = SUPPORT

To turn tracing off, which you definitely want to do after your debugging session is complete, use 0 or OFF:

TRACE_LEVEL_CLIENT = OFF