24. Tracing python-oracledb

24.1. Application Tracing

There are multiple approaches for application tracing and monitoring:

24.1.1. Oracle Database End-to-End Tracing

Oracle Database end-to-end application tracing simplifies diagnosing application code flow and performance problems in multi-tier or multi-user environments.

The connection attributes, client_identifier, clientinfo, dbop, module and action, set the metadata for end-to-end tracing. You can query data dictionary and dynamic performance views to monitor applications, or you can use tracing utilities.

After attributes are set, the values are sent to the database when the next round-trip to the database occurs, for example when the next SQL statement is executed.

The attribute values will remain set in connections released back to a connection pool. When the application re-acquires a connection from the pool, it should initialize the values to a desired state before using that connection.

The example below shows setting the action, module, and client identifier attributes on a connection object, and then querying a view to see the recorded values. The example both sets and queries the values, but typically monitoring is done externally to the application.

# Set the tracing metadata
connection.client_identifier = "pythonuser"
connection.action = "Query Session tracing parameters"
connection.module = "End-to-end Demo"

for row in cursor.execute("""
        SELECT username, client_identifier, module, action

The output will be like:

('SYSTEM', 'pythonuser', 'End-to-end Demo', 'Query Session tracing parameters')

The values can also be manually set as shown by calling DBMS_APPLICATION_INFO procedures or DBMS_SESSION.SET_IDENTIFIER. These incur round-trips to the database; however, reducing scalability.

    DBMS_APPLICATION_INFO.set_module('End-to-End Demo');
    DBMS_APPLICATION_INFO.set_action(action_name => 'Query Session tracing parameters');

The value of Connection.dbop will be shown in the DBOP_NAME column of the V$SQL_MONITOR table:

connection.dbop = "my op"

for row in cursor.execute("""
        SELECT dbop_name
        FROM v$sql_monitor

24.1.2. Subclassing Connections

Subclassing enables applications to add “hooks” for connection and statement execution. This can be used to alter or log connection and execution parameters, and to extend python-oracledb functionality.

The example below demonstrates subclassing a connection to log SQL execution to a file. This example also shows how connection credentials can be embedded in the custom subclass, so application code does not need to supply them.

class Connection(oracledb.Connection):
    log_file_name = "log.txt"

    def __init__(self):
        connect_string = "hr/hr_password@dbhost.example.com/orclpdb"
        self._log("Connect to the database")
        return super(Connection, self).__init__(connect_string)

    def _log(self, message):
        with open(self.log_file_name, "a") as f:
            print(message, file=f)

    def execute(self, sql, parameters):
        cursor = self.cursor()
            return cursor.execute(sql, parameters)
        except oracledb.Error as e:
            error_obj, = e.args

connection = Connection()
        select department_name
        from departments
        where department_id = :id""", dict(id=270))

The messages logged in log.txt are:

Connect to the database

            select department_name
            from departments
            where department_id = :id

If an error occurs, perhaps due to a missing table, the log file would contain instead:

Connect to the database

            select department_name
            from departments
            where department_id = :id
ORA-00942: table or view does not exist

In production applications, be careful not to log sensitive information.

See Subclassing.py for an example.

24.1.3. Debugging PL/SQL with the Java Debug Wire Protocol

The Java Debug Wire Protocol (JDWP) for debugging PL/SQL can be used with python-oracledb.

Python-oracledb applications that call PL/SQL can step through that PL/SQL code using JDWP in a debugger. This allows Python and PL/SQL code to be debugged in the same debugger environment. You can enable PL/SQL debugging in the python-oracledb modes as follows:

  • If you are using python-oracledb Thick mode, set the ORA_DEBUG_JDWP environment variable to host=hostname;port=portnum indicating where the PL/SQL debugger is running. Then run the application.

  • In the python-oracledb Thin mode, you can additionally set the connection parameter debug_jdwp during connection. This variable defaults to the value of the ORA_DEBUG_JDWP environment variable.

See DBMS_DEBUG_JDWP and Debugging PL/SQL from ASP.NET and Visual Studio.

24.1.4. Low Level SQL Tracing

The Thick mode of python-oracledb is implemented using the ODPI-C wrapper on top of the Oracle Client libraries. The ODPI-C tracing capability can be used to log executed python-oracledb statements to the standard error stream. Before executing Python, set the environment variable DPI_DEBUG_LEVEL to 16.

At a Windows command prompt, this could be done with:


On Linux, you might use:


After setting the variable, run the Python Script, for example on Linux:

python end-to-endtracing.py 2> log.txt

For an application that does a single query, the log file might contain a tracing line consisting of the prefix ‘ODPI’, a thread identifier, a timestamp, and the SQL statement executed:

ODPI [26188] 2019-03-26 09:09:03.909: ODPI-C 3.1.1
ODPI [26188] 2019-03-26 09:09:03.909: debugging messages initialized at level 16
ODPI [26188] 2019-03-26 09:09:09.917: SQL SELECT * FROM jobss
Traceback (most recent call last):
File "end-to-endtracing.py", line 14, in <module>
  cursor.execute("select * from jobss")
oracledb.DatabaseError: ORA-00942: table or view does not exist

See ODPI-C Debugging for documentation on DPI_DEBUG_LEVEL.

24.1.5. Using Connection Identifiers

A unique connection identifier (CONNECTION_ID) is generated for each connection to the Oracle Database. The connection identifier is shown in some Oracle Network error messages and logs, which helps in better tracing and diagnosing of connection failures. For example:

DPY-6005: cannot connect to database (CONNECTION_ID=m0PfUY6hYSmWPcgrHZCQIQ==)

You can define a prefix value which is added to the beginning of the CONNECTION_ID. This prefix aids in identifying the connections from a specific application.

In python-oracledb Thin mode, you can specify a prefix in the connection_id_prefix parameter when creating standalone connections, or pooled connections. Also, you can specify the connection identifier in oracledb.ConnectParams() or oracledb.PoolParams(). For example:

connection = oracledb.connect(user="hr", password=userpwd,

If this connection to the database fails, MYAPP is added as a prefix to the CONNECTION_ID as shown in the error message below:

DPY-6005: cannot connect to database (CONNECTION_ID=MYAPPm0PfUY6hYSmWPcgrHZCQIQ==).

In python-oracledb Thick mode, you can specify the connection identifier prefix in a connection string. For example:

         (ADDRESS_LIST= (ADDRESS=...) (ADDRESS=...))

Depending on the Oracle Database version in use, the information that is shown in logs varies.

See Troubleshooting Oracle Net Services for more information on connection identifiers.

24.2. Finding the python-oracledb Mode

The boolean attributes Connection.thin and ConnectionPool.thin can be used to show the current mode of a python-oracledb connection or pool, respectively. The python-oracledb version can be shown with oracledb.__version__.

The information can also be seen in the Oracle Database data dictionary table V$SESSION_CONNECT_INFO:

with connection.cursor() as cursor:
             WHERE SID = SYS_CONTEXT('USERENV', 'SID')"""
    for r, in cursor.execute(sql):

In the python-oracledb Thin mode, the output will be:

python-oracledb thn : 1.0.0

In the python-oracledb Thick mode, the output will be:

python-oracledb thk : 1.0.0

The CLIENT_DRIVER values is configurable in the python-oracledb Thick mode with a call like init_oracle_client(driver_name='myapp : 2.0.0'). See Other python-oracledb Thick Mode Initialization.

24.3. Database Views

This section shows some sample column values for database views. Other views also contain useful information, such as the DRCP views discussed in Monitoring DRCP.


The following table lists sample values for some V$SESSION_CONNECT_INFO columns:

Table 24.1 Sample V$SESSION_CONNECT_INFO column values


Thick value

Thin value


The Oracle Client or Instant Client type, such as “Full Instant Client”



The Oracle Client library version number

“” (the python-oracledb version number with an extra .0.0)


“python-oracledb thk : 1.0.0”

“python-oracledb thn : 1.0.0”

24.3.2. V$SESSION

The following table list sample values for columns with differences in V$SESSION.

Table 24.2 Sample V$SESSION column values


Thick value

Thin value


similar to ttys001

the string “unknown”


similar to python@myuser-mac2 (TNS V1-V3)

the contents of Python’s sys.executable, such as /Users/myuser/.pyenv/versions/3.9.6/bin/python


similar to python@myuser-mac2 (TNS V1-V3)

the contents of Python’s sys.executable, such as /Users/myuser/.pyenv/versions/3.9.6/bin/python

The MODULE column value can be set as shown in Oracle Database End-to-End Tracing.

24.4. Low Level Python-oracledb Driver Tracing

Low level tracing is mostly useful to maintainers of python-oracledb.

  • For the python-oracledb Thin mode, packets can be traced by setting the environment variable:


    Output goes to stdout. The logging is similar to an Oracle Net trace of level 16.

  • The python-oracledb Thick mode can be traced using: