Applies to:

JDBC - Version 11.2.0.1.0 to 23.5 [Release 11.2 to 23]
Information in this document applies to any platform.

Goal

The purpose of this document is to illustrate the JDBC tracing feature. This is also referred as JDBC logging.

JDBC logging is now documented in the JDBC Developer's guide:

Oracle Database JDBC Developer's Guide,
11g Release 2 (11.2)
Part Number E16548-02
31 Diagnosability in JDBC
Logging

Oracle® Database JDBC Developer's Guide
12c Release 1 (12.1)
E49300-05
32 Diagnosability in JDBC
Logging

JDBC Developer's Guide Release 19
34 Diagnosability in JDBC
34.1 About Logging Feature of Oracle JDBC Drivers

JDBC Developer's Guide and Reference Release 21
36 Diagnosability in JDBC
36.1 About Logging Feature of Oracle JDBC Drivers

Solution

In this 6 minute video, you will learn about JDBC logging and tracing

A Quick Video on How to Enable Oracle JDBC Logging & Packet Tracing(Doc ID 2975841.1) Direct Link

The JDBC driver tracing facility utilizes the package java.util.logging. There are two ways to initiate JDBC tracing:

use system properties
java.util.logging API

This document introduces the tracing loggers and levels then describes two approaches to implement tracing.

Tracing Loggers

The user has fine grained control over the tracing by setting specific loggers. The tracing loggers are:

oracle.jdbc
oracle.jdbc.driver
oracle.jdbc.datum
oracle.jdbc.adt
oracle.jdbc.conversion
oracle.jdbc.thin
oracle.jdbc.kprb
oracle.jdbc.pool
oracle.jdbc.xa
oracle.jdbc.sqlj
oracle.jdbc.oci
oracle.jdbc.jpub
oracle.net
oracle.net.ns

Tracing Levels

For every logger there are different levels one can set. Depending on the information required one can specify general level with less output or very detailed output. The tracing levels can have the following values:

INFO
- JDBC API level tracing
- expected volume: low
SEVERE
- Program error eg. an unexpected value encountered in a switch statement
- Error conditions that usually lead to catastrophic or unrecoverable results
- expected volume: low
WARNING
- Error conditions that are usually recoverable
- expected volume: low
FINE
- Function entry/return information
- expected volume: medium
FINER
- High-level debug information
- expected volume: medium
FINEST
- Detail debug information
- expected volume: high
CONFIG
- SQL string
- expected volume: low

Tracing JDBC Using System Properties

To enable the logging by using System properties you will need a so called properties file. The JDBC directory has a complete sample logging properties file called OracleLog.properties. This file is located in $ORACLE_HOME/jdbc/demo provided you have installed the demonstrations using the Database Companion CD. This file controls the tracing levels, tracing loggers, and output formatting. This approach offers the advantages of being able to set tracing without having to recompile the application. Use either the properties file provided in the demo directory or build your own based on the sample provided in this note. To use this way of tracing do following steps:

Use the debug library of the JDBC Driver, such as ojdbc6_g.jar 11g driver, ojdbc7_g.jar 12.1 driver or the ojdbc8_g.jar for versions 12.2 and higher
Create a properties file to set the different levels of tracing, for example:
handlers=java.util.logging.ConsoleHandler,java.util.logging.FileHandler

# default file output is in user's home directory.
java.util.logging.FileHandler.pattern = jdbc.log

# example of a full pathname in Windows
#java.util.logging.FileHandler.pattern=<DIR>\\<NAME>.log

java.util.logging.FileHandler.limit = 50000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.formatter = java.util.logging.SimpleFormatter
java.util.logging.FileHandler.append = true

# Predefined levels are: ALL, SEVERE, WARNING, INFO, CONFIG, FINE, FINER,
# FINEST, OFF

# Setting this to SEVERE avoids duplicate output from default logger
java.util.logging.ConsoleHandler.level = SEVERE
java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter
oracle.jdbc.level = FINEST
To run the test with tracing enabled use following command:
java -Djava.util.logging.config.file=<properties file location> -Doracle.jdbc.Trace=true <program name>

Tracing JDBC Using java.util.logging API

You can programmatically enable or disable logging by using the Diagnosability MBean.

Starting from Oracle Database 12c Release 2 (12.2.0.1), JDBC provides support for enabling and disabling feature-specific logging for selected features during runtime.

See details in the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/JDBC-diagnosability.html#GUID-37D49DEF-5A0C-4045-85EA-77DF3B6E4642
JDBC Developer's Guide and Reference
36 Diagnosability in JDBC

The following Technical Brief on the Oracle Technology Network (OTN) may also be helpful:

Oracle JDBC Logging using java.util.logging

Managing log files

The JDBC logging may produce very large amounts of output. Storing this log output in a single file may result in a very large file that is unmanageable. For details on this topic please see Note 944655.1 - How to Limit JDBC Logging Output.

NOTE:
In JDBC 23ai, the core JDBC Jars (i.e., ojdbc8.jar or ojdbc11.jar) include the logging capabilities. That is, there is no more switching between the production jar and the debug jar (_g).
The logging capabilities need to be turned on using the following properties:

-Doracle.jdbc.diagnostic.enableLogging=true.
-Djava.util.logging.config.file=./logging.config

The handler as well as the granularity of the logging must also be specified.
Please see Diagnosability in JDBC for more details.

Join the Java Development MOS Community forum for general discussions, questions, best practices, and other valuable information on: Oracle JDeveloper and ADF, Oracle WebLogic - JEE Programming (EJB, JMS etc), Oracle JDBC, Oracle Web Services (incl. DBWS Callout Utility), Oracle Web Services Manager (OWSM), Oracle Service Registry (OSR), Oracle Toplink (EclipseLink), Sun NetBeans IDE / Java Studio Creator & Java Studio Enterprise, OC4J, KODO.