Connecting to Oracle Database


cx_Oracle has a major new release under a new name and homepage python-oracledb.

New projects should install python-oracledb instead of cx_Oracle.

Connections between cx_Oracle and Oracle Database are used for executing SQL, PL/SQL, and SODA.

Establishing Database Connections

There are two ways to connect to Oracle Database using cx_Oracle:

  • Standalone connections

    These are useful when the application maintains a single user session to a database. Connections are created by cx_Oracle.connect() or its alias cx_Oracle.Connection().

  • Pooled connections

    Connection pooling is important for performance when applications frequently connect and disconnect from the database. Pools support Oracle’s high availability features and are recommended for applications that must be reliable. Small pools can also be useful for applications that want a few connections available for infrequent use. Pools are created with cx_Oracle.SessionPool() at application initialization time, and then SessionPool.acquire() can be called to obtain a connection from a pool.

Many connection behaviors can be controlled by cx_Oracle options. Other settings can be configured in Optional Oracle Net Configuration Files or in Optional Oracle Client Configuration Files. These include limiting the amount of time that opening a connection can take, or enabling network encryption.

Example: Standalone Connection to Oracle Database

import cx_Oracle

userpwd = ". . ." # Obtain password string from a user prompt or environment variable

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

cx_Oracle also supports external authentication so passwords do not need to be in the application.

Closing Connections

Connections should be released when they are no longer needed by calling Connection.close(). Alternatively, you may prefer to let connections be automatically cleaned up when references to them go out of scope. This lets cx_Oracle close dependent resources in the correct order. One other approach is the use of a “with” block, for example:

with cx_Oracle.connect(user=user, password=password,
                       encoding="UTF-8") as connection:
    cursor = connection.cursor()
    cursor.execute("insert into SomeTable values (:1, :2)",
                   (1, "Some string"))

This code ensures that, once the block is completed, the connection is closed and resources have been reclaimed by the database. In addition, any attempt to use the variable connection outside of the block will simply fail.

Prompt closing of connections is important when using connection pools so connections are available for reuse by other pool users.

Connection Strings

The data source name parameter dsn of cx_Oracle.connect() and cx_Oracle.SessionPool() is the Oracle Database connection string identifying which database service to connect to. The dsn string can be one of:

  • An Oracle Easy Connect string

  • An Oracle Net Connect Descriptor string

  • A Net Service Name mapping to a connect descriptor

For more information about naming methods, see Oracle Net Service Reference.

Easy Connect Syntax for Connection Strings

An Easy Connect string is often the simplest connection string to use for the data source name parameter dsn of cx_Oracle.connect() and cx_Oracle.SessionPool(). This method does not need configuration files such as tnsnames.ora.

For example, to connect to the Oracle Database service orclpdb1 that is running on the host with the default Oracle Database port 1521, use:

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

If the database is using a non-default port, it must be specified:

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

The Easy Connect syntax supports Oracle Database service names. It cannot be used with the older System Identifiers (SID).

The Easy Connect syntax has been extended in recent versions of Oracle Database client since its introduction in 10g. Check the Easy Connect Naming method in Oracle Net Service Administrator’s Guide for the syntax to use in your version of the Oracle Client libraries.

If you are using Oracle Client 19c, the latest Easy Connect Plus syntax allows the use of multiple hosts or ports, along with optional entries for the wallet location, the distinguished name of the database server, and even lets some network configuration options be set. This means that a sqlnet.ora file is not needed for some common connection scenarios.

Oracle Net Connect Descriptor Strings

The cx_Oracle.makedsn() function can be used to construct a connect descriptor string for the data source name parameter dsn of cx_Oracle.connect() and cx_Oracle.SessionPool(). The makedsn() function accepts the database hostname, the port number, and the service name. It also supports sharding syntax.

For example, to connect to the Oracle Database service orclpdb1 that is running on the host with the default Oracle Database port 1521, use:

dsn = cx_Oracle.makedsn("", 1521, service_name="orclpdb1")
connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,

Note the use of the named argument service_name. By default, the third parameter of makedsn() is a database System Identifier (SID), not a service name. However, almost all current databases use service names.

The value of dsn in this example is the connect descriptor string:


You can manually create similar connect descriptor strings. This lets you extend the syntax, for example to support failover. These strings can be embedded directly in the application:

dsn = """(DESCRIPTION=

connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,

Net Service Names for Connection Strings

Connect Descriptor Strings are commonly stored in a tnsnames.ora file and associated with a Net Service Name. This name can be used directly for the data source name parameter dsn of cx_Oracle.connect() and cx_Oracle.SessionPool(). For example, given a tnsnames.ora file with the following contents:

    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
      (SERVICE_NAME = orclpdb1)

then you could connect using the following code:

connection = cx_Oracle.connect(user="hr", password=userpwd, dsn="orclpdb1",

For more information about Net Service Names, see Database Net Services Reference.

JDBC and Oracle SQL Developer Connection Strings

The cx_Oracle connection string syntax is different to Java JDBC and the common Oracle SQL Developer syntax. If these JDBC connection strings reference a service name like:


for example:

then use Oracle’s Easy Connect syntax in cx_Oracle:

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

Alternatively, if a JDBC connection string uses an old-style Oracle SID “system identifier”, and the database does not have a service name:


for example:

then a connect descriptor string from makedsn() can be used in the application:

dsn = cx_Oracle.makedsn("", 1521, sid="orcl")
connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,

Alternatively, create a tnsnames.ora (see Optional Oracle Net Configuration Files) entry, for example:

finance =
   (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
     (SID = ORCL)

This can be referenced in cx_Oracle:

connection = cx_Oracle.connect(user="hr", password=userpwd, dsn="finance",

Connection Pooling

cx_Oracle’s connection pooling lets applications create and maintain a pool of connections to the database. Connection pooling is important for performance when applications frequently connect and disconnect from the database. The pool implementation uses Oracle’s session pool technology which supports Oracle’s high availability features and is recommended for applications that must be reliable. This also means that small pools can be useful for applications that want a few connections available for infrequent use.

A connection pool is created by calling SessionPool(). This is generally called during application initialization. The initial pool size and the maximum pool size are provided at the time of pool creation. When the pool needs to grow, new connections are created automatically. The pool can shrink back to the minimum size when connections are no longer in use. For pools created with external authentication, with homogeneous set to False, or when using Database Resident Connection Pooling (DRCP), then the number of connections initially created is zero even if a larger value is specified for min. Also in these cases the pool increment is always 1, regardless of the value of increment.

After a pool has been created, connections can be obtained from it by calling acquire(). These connections can be used in the same way that standalone connections are used.

Connections acquired from the pool should be released back to the pool using SessionPool.release() or Connection.close() when they are no longer required. Otherwise, they will be released back to the pool automatically when all of the variables referencing the connection go out of scope. This make connections available for other users of the pool.

The session pool can be completely closed using SessionPool.close().

The example below shows how to connect to Oracle Database using a connection pool:

# Create the session pool
pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
                             dsn="", min=2,
                             max=5, increment=1, encoding="UTF-8")

# Acquire a connection from the pool
connection = pool.acquire()

# Use the pooled connection
cursor = connection.cursor()
for result in cursor.execute("select * from mytab"):

# Release the connection to the pool

# Close the pool

Other cx_Oracle.SessionPool() options can be used at pool creation. For example the getmode value can be set so that any acquire() call will wait for a connection to become available if all are currently in use, for example:

# Create the session pool
pool = cx_Oracle.SessionPool(user="hr", password=userpwd,
                             dsn="", min=2,
                             max=5, increment=1,

See for an example.

Before SessionPool.acquire() returns, cx_Oracle does a lightweight check to see if the network transport for the selected connection is still open. If it is not, then acquire() will clean up the connection and return a different one. This check will not detect cases such as where the database session has been killed by the DBA, or reached a database resource manager quota limit. To help in those cases, acquire() will also do a full round-trip ping to the database when it is about to return a connection that was unused in the pool for SessionPool.ping_interval seconds. If the ping fails, the connection will be discarded and another one obtained before acquire() returns to the application. Because this full ping is time based, it won’t catch every failure. Also network timeouts and session kills may occur after acquire() and before Cursor.execute(). To handle these cases, applications need to check for errors after each execute() and make application-specific decisions about retrying work if there was a connection failure. Oracle’s Application Continuity can do this automatically in some cases. Note both the lightweight and full ping connection checks can mask performance-impacting configuration issues, for example firewalls killing connections, so monitor the connection rate in AWR for an unexpected value. You can explicitly initiate a full ping to check connection liveness with but overuse will impact performance and scalability.

Connection Pool Sizing

The Oracle Real-World Performance Group’s recommendation is to use fixed size connection pools. The values of min and max should be the same (and the increment equal to zero). This avoids connection storms which can decrease throughput. See Guideline for Preventing Connection Storms: Use Static Pools, which contains more details about sizing of pools. Having a fixed size will guarantee that the database can handle the upper pool size. For example, if a pool needs to grow but the database resources are limited, then SessionPool.acquire() may return errors such as ORA-28547. With a fixed pool size, this class of error will occur when the pool is created, allowing you to change the size before users access the application. With a dynamically growing pool, the error may occur much later after the pool has been in use for some time.

The Real-World Performance Group also recommends keeping pool sizes small, as they may perform better than larger pools. The pool attributes should be adjusted to handle the desired workload within the bounds of available resources in cx_Oracle and the database.

Make sure the firewall, resource manager or user profile IDLE_TIME do not expire idle sessions, since this will require connections be recreated, which will impact performance and scalability.

Connection Pool Reconfiguration

Some pool settings can be changed dynamically with SessionPool.reconfigure(). This allows the pool size and other attributes to be changed during application runtime without needing to restart the pool or application.

For example a pool’s size can be changed like:

pool.reconfigure(min=10, max=10, increment=0)

After any size change has been processed, reconfiguration on the other parameters is done sequentially. If an error such as an invalid value occurs when changing one attribute, then an exception will be generated but any already changed attributes will retain their new values.

During reconfiguration of a pool’s size, the behavior of SessionPool.acquire() depends on the getmode in effect when acquire() is called, see SessionPool.reconfigure(). Closing connections or closing the pool will wait until after pool reconfiguration is complete.

Calling reconfigure() is the only way to change a pool’s min, max and increment values. Other attributes such as wait_timeout can also be passed to reconfigure() or they can be set directly:

pool.wait_timeout = 1000

Session CallBacks for Setting Pooled Connection State

Applications can set “session” state in each connection. Examples of session state are NLS settings from ALTER SESSION statements. Pooled connections will retain their session state after they have been released back to the pool. However, because pools can grow, or connections in the pool can be recreated, there is no guarantee a subsequent acquire() call will return a database connection that has any particular state.

The SessionPool() parameter session_callback enables efficient setting of session state so that connections have a known session state, without requiring that state to be explicitly set after each acquire() call.

Connections can also be tagged when they are released back to the pool. The tag is a user-defined string that represents the session state of the connection. When acquiring connections, a particular tag can be requested. If a connection with that tag is available, it will be returned. If not, then another session will be returned. By comparing the actual and requested tags, applications can determine what exact state a session has, and make any necessary changes.

The session callback can be a Python function or a PL/SQL procedure.

There are three common scenarios for session_callback:

  • When all connections in the pool should have the same state, use a Python callback without tagging.

  • When connections in the pool require different state for different users, use a Python callback with tagging.

  • When using Database Resident Connection Pooling (DRCP): use a PL/SQL callback with tagging.

Python Callback

If the session_callback parameter is a Python procedure, it will be called whenever acquire() will return a newly created database connection that has not been used before. It is also called when connection tagging is being used and the requested tag is not identical to the tag in the connection returned by the pool.

An example is:

# Set the NLS_DATE_FORMAT for a session
def init_session(connection, requested_tag):
    cursor = connection.cursor()

# Create the pool with session callback defined
pool = cx_Oracle.SessionPool(user="hr", password=userpwd, dsn="orclpdb1",

# Acquire a connection from the pool (will always have the new date format)
connection = pool.acquire()

If needed, the init_session() procedure is called internally before acquire() returns. It will not be called when previously used connections are returned from the pool. This means that the ALTER SESSION does not need to be executed after every acquire() call. This improves performance and scalability.

In this example tagging was not being used, so the requested_tag parameter is ignored.

Note: if you need to execute multiple SQL statements in the callback, use an anonymous PL/SQL block to save round-trips of repeated execute() calls. With ALTER SESSION, pass multiple settings in the one statement:

            execute immediate
                    'alter session set nls_date_format = ''YYYY-MM-DD'' nls_language = AMERICAN';
            -- other SQL statements could be put here

Connection Tagging

Connection tagging is used when connections in a pool should have differing session states. In order to retrieve a connection with a desired state, the tag attribute in acquire() needs to be set.

When cx_Oracle is using Oracle Client libraries 12.2 or later, then cx_Oracle uses ‘multi-property tags’ and the tag string must be of the form of one or more “name=value” pairs separated by a semi-colon, for example "loc=uk;lang=cy".

When a connection is requested with a given tag, and a connection with that tag is not present in the pool, then a new connection, or an existing connection with cleaned session state, will be chosen by the pool and the session callback procedure will be invoked. The callback can then set desired session state and update the connection’s tag. However if the matchanytag parameter of acquire() is True, then any other tagged connection may be chosen by the pool and the callback procedure should parse the actual and requested tags to determine which bits of session state should be reset.

The example below demonstrates connection tagging:

def init_session(connection, requested_tag):
    if requested_tag == "NLS_DATE_FORMAT=SIMPLE":
    elif requested_tag == "NLS_DATE_FORMAT=FULL":
    cursor = connection.cursor()
    connection.tag = requested_tag

pool = cx_Oracle.SessionPool(user="hr", password=userpwd, dsn="orclpdb1",

# Two connections with different session state:
connection1 = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")
connection2 = pool.acquire(tag="NLS_DATE_FORMAT=FULL")

See for an example.

PL/SQL Callback

When cx_Oracle uses Oracle Client 12.2 or later, the session callback can also be the name of a PL/SQL procedure. A PL/SQL callback will be initiated only when the tag currently associated with a connection does not match the tag that is requested. A PL/SQL callback is most useful when using Database Resident Connection Pooling (DRCP) because DRCP does not require a round-trip to invoke a PL/SQL session callback procedure.

The PL/SQL session callback should accept two VARCHAR2 arguments:

PROCEDURE myPlsqlCallback (
    requestedTag IN  VARCHAR2,
    actualTag    IN  VARCHAR2

The logic in this procedure can parse the actual tag in the session that has been selected by the pool and compare it with the tag requested by the application. The procedure can then change any state required before the connection is returned to the application from acquire().

If the matchanytag attribute of acquire() is True, then a connection with any state may be chosen by the pool.

Oracle ‘multi-property tags’ must be used. The tag string must be of the form of one or more “name=value” pairs separated by a semi-colon, for example "loc=uk;lang=cy".

In cx_Oracle set session_callback to the name of the PL/SQL procedure. For example:

pool = cx_Oracle.SessionPool(user="hr", password=userpwd,

connection = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE",
                          # DRCP options, if you are using DRCP

See for an example.

Heterogeneous and Homogeneous Connection Pools

By default, connection pools are ‘homogeneous’, meaning that all connections use the same database credentials. However, if the pool option homogeneous is False at the time of pool creation, then a ‘heterogeneous’ pool will be created. This allows different credentials to be used each time a connection is acquired from the pool with acquire().

Heterogeneous Pools

When a heterogeneous pool is created by setting homogeneous to False and no credentials are supplied during pool creation, then a user name and password may be passed to acquire() as shown in this example:

pool = cx_Oracle.SessionPool(dsn="",
                             homogeneous=False, encoding="UTF-8")
connection = pool.acquire(user="hr", password=userpwd)

Database Resident Connection Pooling (DRCP)

Database Resident Connection Pooling (DRCP) enables database resource sharing for applications that run in multiple client processes, or run on multiple middle-tier application servers. By default each connection from Python will use one database server process. DRCP allows pooling of these server processes. This reduces the amount of memory required on the database host. The DRCP pool can be shared by multiple applications.

DRCP is useful for applications which share the same database credentials, have similar session settings (for example date format settings or PL/SQL package state), and where the application gets a database connection, works on it for a relatively short duration, and then releases it.

Applications can choose whether or not to use pooled connections at runtime.

For efficiency, it is recommended that DRCP connections should be used in conjunction with cx_Oracle’s local connection pool.

Using DRCP in Python

Using DRCP with cx_Oracle applications involves the following steps:

  1. Configuring and enabling DRCP in the database

  2. Configuring the application to use a DRCP connection

  3. Deploying the application

Configuring and enabling DRCP

Every instance of Oracle Database uses a single, default connection pool. The pool can be configured and administered by a DBA using the DBMS_CONNECTION_POOL package:

    minsize => 4,
    maxsize => 40,
    incrsize => 2,
    session_cached_cursors => 20,
    inactivity_timeout => 300,
    max_think_time => 600,
    max_use_session => 500000,
    max_lifetime_session => 86400)

Alternatively the method DBMS_CONNECTION_POOL.ALTER_PARAM() can set a single parameter:

    param_name => 'MAX_THINK_TIME',
    param_value => '1200')

The inactivity_timeout setting terminates idle pooled servers, helping optimize database resources. To avoid pooled servers permanently being held onto by a selfish Python script, the max_think_time parameter can be set. The parameters num_cbrok and maxconn_cbrok can be used to distribute the persistent connections from the clients across multiple brokers. This may be needed in cases where the operating system per-process descriptor limit is small. Some customers have found that having several connection brokers improves performance. The max_use_session and max_lifetime_session parameters help protect against any unforeseen problems affecting server processes. The default values will be suitable for most users. See the Oracle DRCP documentation for details on parameters.

In general, if pool parameters are changed, the pool should be restarted, otherwise server processes will continue to use old settings.

There is a DBMS_CONNECTION_POOL.RESTORE_DEFAULTS() procedure to reset all values.

When DRCP is used with RAC, each database instance has its own connection broker and pool of servers. Each pool has the identical configuration. For example, all pools start with minsize server processes. A single DBMS_CONNECTION_POOL command will alter the pool of each instance at the same time. The pool needs to be started before connection requests begin. The command below does this by bringing up the broker, which registers itself with the database listener:


Once enabled this way, the pool automatically restarts when the database instance restarts, unless explicitly stopped with the DBMS_CONNECTION_POOL.STOP_POOL() command:


The pool cannot be stopped while connections are open.

Application Deployment for DRCP

In order to use DRCP, the cclass and purity parameters should be passed to cx_Oracle.connect() or SessionPool.acquire(). If cclass is not set, the pooled server sessions will not be reused optimally, and the DRCP statistic views will record large values for NUM_MISSES.

The DRCP purity can be one of ATTR_PURITY_NEW, ATTR_PURITY_SELF, or ATTR_PURITY_DEFAULT. The value ATTR_PURITY_SELF allows reuse of both the pooled server process and session memory, giving maximum benefit from DRCP. See the Oracle documentation on benefiting from scalability.

The connection string used for connect() or acquire() must request a pooled server by following one of the syntaxes shown below:

Using Oracle’s Easy Connect syntax, the connection would look like:

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

Or if you connect using a Net Service Name named customerpool:

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               dsn="customerpool", encoding="UTF-8")

Then only the Oracle Network configuration file tnsnames.ora needs to be modified:


If these changes are made and the database is not actually configured for DRCP, or the pool is not started, then connections will not succeed and an error will be returned to the Python application.

Although applications can choose whether or not to use pooled connections at runtime, care must be taken to configure the database appropriately for the number of expected connections, and also to stop inadvertent use of non-DRCP connections leading to a database server resource shortage. Conversely, avoid using DRCP connections for long-running operations.

The example below shows how to connect to Oracle Database using Database Resident Connection Pooling:

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

The example below shows connecting to Oracle Database using DRCP and cx_Oracle’s connection pooling:

mypool = cx_Oracle.SessionPool(user="hr", password=userpwd,
connection = mypool.acquire(cclass="MYCLASS",

For more information about DRCP see Oracle Database Concepts Guide, and for DRCP Configuration see Oracle Database Administrator’s Guide.

Closing Connections

Python scripts where cx_Oracle connections do not go out of scope quickly (which releases them), or do not currently use Connection.close(), should be examined to see if close() can be used, which then allows maximum use of DRCP pooled servers by the database:

 # Do some database operations
connection = mypool.acquire(cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF)
. . .

# Do lots of non-database work
. . .

# Do some more database operations
connection = mypool.acquire(cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF)
. . .

Monitoring DRCP

Data dictionary views are available to monitor the performance of DRCP. Database administrators can check statistics such as the number of busy and free servers, and the number of hits and misses in the pool against the total number of requests from clients. The views are:








DBA_CPOOL_INFO displays configuration information about the DRCP pool. The columns are equivalent to the dbms_connection_pool.configure_pool() settings described in the table of DRCP configuration options, with the addition of a STATUS column. The status is ACTIVE if the pool has been started and INACTIVE otherwise. Note the pool name column is called CONNECTION_POOL. This example checks whether the pool has been started and finds the maximum number of pooled servers:

SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info;

---------------------------- ---------- ----------


The V$SESSION view shows information about the currently active DRCP sessions. It can also be joined with V$PROCESS via V$SESSION.PADDR = V$PROCESS.ADDR to correlate the views.


The V$CPOOL_STATS view displays information about the DRCP statistics for an instance. The V$CPOOL_STATS view can be used to assess how efficient the pool settings are. T his example query shows an application using the pool effectively. The low number of misses indicates that servers and sessions were reused. The wait count shows just over 1% of requests had to wait for a pooled server to become available:

------------ ---------- ---------- ----------
       10031      99990         40       1055

If cclass was set (allowing pooled servers and sessions to be reused) then NUM_MISSES will be low. If the pool maxsize is too small for the connection load, then NUM_WAITS will be high.


The view V$CPOOL_CC_STATS displays information about the connection class level statistics for the pool per instance:

SQL> SELECT cclass_name, num_requests, num_hits, num_misses
     FROM v$cpool_cc_stats;

-------------------------------- ------------ ---------- ----------
HR.MYCLASS                             100031      99993         38


The V$POOL_CONN_INFO view gives insight into client processes that are connected to the connection broker, making it easier to monitor and trace applications that are currently using pooled servers or are idle. This view was introduced in Oracle 11gR2.

You can monitor the view V$CPOOL_CONN_INFO to, for example, identify misconfigured machines that do not have the connection class set correctly. This view maps the machine name to the class name:

SQL> SELECT cclass_name, machine FROM v$cpool_conn_info;

CCLASS_NAME                             MACHINE
--------------------------------------- ------------
CJ.OCI:SP:wshbIFDtb7rgQwMyuYvodA        cjlinux
. . .

In this example you would examine applications on cjlinux and make sure cclass is set.

Connecting Using Proxy Authentication

Proxy authentication allows a user (the “session user”) to connect to Oracle Database using the credentials of a ‘proxy user’. Statements will run as the session user. Proxy authentication is generally used in three-tier applications where one user owns the schema while multiple end-users access the data. For more information about proxy authentication, see the Oracle documentation.

An alternative to using proxy users is to set Connection.client_identifier after connecting and use its value in statements and in the database, for example for monitoring.

The following proxy examples use these schemas. The mysessionuser schema is granted access to use the password of myproxyuser:

CREATE USER myproxyuser IDENTIFIED BY myproxyuserpw;

CREATE USER mysessionuser IDENTIFIED BY itdoesntmatter;

ALTER USER mysessionuser GRANT CONNECT THROUGH myproxyuser;

After connecting to the database, the following query can be used to show the session and proxy users:


Standalone connection examples:

# Basic Authentication without a proxy
connection = cx_Oracle.connect(user="myproxyuser",
# PROXY_USER:   None

# Basic Authentication with a proxy
connection = cx_Oracle.connect(user="myproxyuser[mysessionuser]",

Pooled connection examples:

# Basic Authentication without a proxy
pool = cx_Oracle.SessionPool(user="myproxyuser", password="myproxyuser",
connection = pool.acquire()
# PROXY_USER:   None

# Basic Authentication with proxy
pool = cx_Oracle.SessionPool(user="myproxyuser[mysessionuser]",
                             homogeneous=False, encoding="UTF-8")
connection = pool.acquire()

Note the use of a heterogeneous pool in the example above. This is required in this scenario.

Connecting Using External Authentication

Instead of storing the database username and password in Python scripts or environment variables, database access can be authenticated by an outside system. External Authentication allows applications to validate user access by an external password store (such as an Oracle Wallet), by the operating system, or with an external authentication service.

Using an Oracle Wallet for External Authentication

The following steps give an overview of using an Oracle Wallet. Wallets should be kept securely. Wallets can be managed with Oracle Wallet Manager.

In this example the wallet is created for the myuser schema in the directory /home/oracle/wallet_dir. The mkstore command is available from a full Oracle client or Oracle Database installation. If you have been given wallet by your DBA, skip to step 3.

  1. First create a new wallet as the oracle user:

    mkstore -wrl "/home/oracle/wallet_dir" -create

    This will prompt for a new password for the wallet.

  2. Create the entry for the database user name and password that are currently hardcoded in your Python scripts. Use either of the methods shown below. They will prompt for the wallet password that was set in the first step.

    Method 1 - Using an Easy Connect string:

    mkstore -wrl "/home/oracle/wallet_dir" -createCredential myuser myuserpw

    Method 2 - Using a connect name identifier:

    mkstore -wrl "/home/oracle/wallet_dir" -createCredential mynetalias myuser myuserpw

    The alias key mynetalias immediately following the -createCredential option will be the connect name to be used in Python scripts. If your application connects with multiple different database users, you could create a wallet entry with different connect names for each.

    You can see the newly created credential with:

    mkstore -wrl "/home/oracle/wallet_dir" -listCredential
  3. Skip this step if the wallet was created using an Easy Connect String. Otherwise, add an entry in tnsnames.ora for the connect name as follows:

    mynetalias =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
            (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = orclpdb1)

    The file uses the description for your existing database and sets the connect name alias to mynetalias, which is the identifier used when adding the wallet entry.

  4. Add the following wallet location entry in the sqlnet.ora file, using the DIRECTORY you created the wallet in:

        (SOURCE =
            (METHOD = FILE)
            (METHOD_DATA =
                (DIRECTORY = /home/oracle/wallet_dir)

    Examine the Oracle documentation for full settings and values.

  5. Ensure the configuration files are in a default location or set TNS_ADMIN is set to the directory containing them. See Optional Oracle Net Configuration Files.

With an Oracle wallet configured, and readable by you, your scripts can connect using:

connection = cx_Oracle.connect(dsn="mynetalias", encoding="UTF-8")


pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False,
                             dsn="mynetalias", encoding="UTF-8")

The dsn must match the one used in the wallet.

After connecting, the query:


will show:



Wallets are also used to configure TLS connections. If you are using a wallet like this, you may need a database username and password in cx_Oracle.connect() and cx_Oracle.SessionPool() calls.

External Authentication and Proxy Authentication

The following examples show external wallet authentication combined with proxy authentication. These examples use the wallet configuration from above, with the addition of a grant to another user:


After connection, you can check who the session user is with:


Standalone connection example:

# External Authentication with proxy
connection = cx_Oracle.connect(user="[mysessionuser]", dsn="mynetalias",

Pooled connection example:

# External Authentication with proxy
pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False,
                             dsn="mynetalias", encoding="UTF-8")

The following usage is not supported:

pool = cx_Oracle.SessionPool(user="[mysessionuser]", externalauth=True,
                             homogeneous=False, dsn="mynetalias",

Operating System Authentication

With Operating System authentication, Oracle allows user authentication to be performed by the operating system. The following steps give an overview of how to implement OS Authentication on Linux.

  1. Login to your computer. The commands used in these steps assume the operating system user name is “oracle”.

  2. Login to SQL*Plus as the SYSTEM user and verify the value for the OS_AUTHENT_PREFIX parameter:

    SQL> SHOW PARAMETER os_authent_prefix
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    os_authent_prefix                    string      ops$
  3. Create an Oracle database user using the os_authent_prefix determined in step 2, and the operating system user name:


In Python, connect using the following code:

connection = cx_Oracle.connect(dsn="mynetalias", encoding="UTF-8")

Your session user will be OPS$ORACLE.

If your database is not on the same computer as python, you can perform testing by setting the database configuration parameter remote_os_authent=true. Beware this is insecure.

See Oracle Database Security Guide for more information about Operating System Authentication.

Privileged Connections

The mode parameter of the function cx_Oracle.connect() specifies the database privilege that you want to associate with the user.

The example below shows how to connect to Oracle Database as SYSDBA:

connection = cx_Oracle.connect(user="sys", password=syspwd,
                               mode=cx_Oracle.SYSDBA, encoding="UTF-8")

cursor = con.cursor()

This is equivalent to executing the following in SQL*Plus:



Securely Encrypting Network Traffic to Oracle Database

You can encrypt data transferred between the Oracle Database and the Oracle Client libraries used by cx_Oracle so that unauthorized parties are not able to view plain text values as the data passes over the network. The easiest configuration is Oracle’s native network encryption. The standard SSL protocol can also be used if you have a PKI, but setup is necessarily more involved.

With native network encryption, the client and database server negotiate a key using Diffie-Hellman key exchange. This provides protection against man-in-the-middle attacks.

Native network encryption can be configured by editing Oracle Net’s optional sqlnet.ora configuration file, on either the database server and/or on each cx_Oracle ‘client’ machine. Parameters control whether data integrity checking and encryption is required or just allowed, and which algorithms the client and server should consider for use.

As an example, to ensure all connections to the database are checked for integrity and are also encrypted, create or edit the Oracle Database $ORACLE_HOME/network/admin/sqlnet.ora file. Set the checksum negotiation to always validate a checksum and set the checksum type to your desired value. The network encryption settings can similarly be set. For example, to use the SHA512 checksum and AES256 encryption use:


If you definitely know that the database server enforces integrity and encryption, then you do not need to configure cx_Oracle separately. However you can also, or alternatively, do so depending on your business needs. Create a sqlnet.ora on your client machine and locate it with other Optional Oracle Net Configuration Files:


The client and server sides can negotiate the protocols used if the settings indicate more than one value is accepted.

Note that these are example settings only. You must review your security requirements and read the documentation for your Oracle version. In particular review the available algorithms for security and performance.

The NETWORK_SERVICE_BANNER column of the database view V$SESSION_CONNECT_INFO can be used to verify the encryption status of a connection.

For more information on Oracle Data Network Encryption and Integrity, configuring SSL network encryption and Transparent Data Encryption of data-at-rest in the database, see Oracle Database Security Guide.

Resetting Passwords

After connecting, passwords can be changed by calling Connection.changepassword():

# Get the passwords from somewhere, such as prompting the user
oldpwd = getpass.getpass(f"Old Password for {username}: ")
newpwd = getpass.getpass(f"New Password for {username}: ")

connection.changepassword(oldpwd, newpwd)

When a password has expired and you cannot connect directly, you can connect and change the password in one operation by using the newpassword parameter of the function cx_Oracle.connect() constructor:

# Get the passwords from somewhere, such as prompting the user
oldpwd = getpass.getpass(f"Old Password for {username}: ")
newpwd = getpass.getpass(f"New Password for {username}: ")

connection = cx_Oracle.connect(user=username, password=oldpwd,
                               newpassword=newpwd, encoding="UTF-8")

Connecting to Oracle Cloud Autonomous Databases

To enable connection to Oracle Autonomous Database in Oracle Cloud, a wallet needs be downloaded from the cloud, and cx_Oracle needs to be configured to use it. The wallet gives mutual TLS which provides enhanced security for authentication and encryption. A database username and password is still required for your application connections.

Install the Wallet and Network Configuration Files

From the Oracle Cloud console for the database, download the wallet zip file. It contains the wallet and network configuration files. Note: keep wallet files in a secure location and share them only with authorized users.

Unzip the wallet zip file. For cx_Oracle, only these files from the zip are needed:

  • tnsnames.ora - Maps net service names used for application connection strings to your database services

  • sqlnet.ora - Configures Oracle Network settings

  • cwallet.sso - Enables SSL/TLS connections

There are now two options:

  • Move the three files to the network/admin directory of the client libraries used by your application. For example if you are using Instant Client 19c and it is in $HOME/instantclient_19_11, then you would put the wallet files in $HOME/instantclient_19_11/network/admin/.

  • Alternatively, move them to any accessible directory, for example /opt/OracleCloud/MYDB.

    Then edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file. For example:


    Since the tnsnames.ora and sqlnet.ora files are not in the default location, your application needs to indicate where they are, either with the config_dir parameter to cx_Oracle.init_oracle_client(), or using the TNS_ADMIN environment variable. See Optional Oracle Net Configuration Files. Neither of these settings are needed, and you don’t need to edit sqlnet.ora, if you have put all the files in the network/admin directory.

Run Your Application

The tnsnames.ora file contains net service names for various levels of database service. For example, if you create a database called CJDB1 with the Always Free services from the Oracle Cloud Free Tier, then you might decide to use the connection string in tnsnames.ora called cjdb1_high.

Update your application to use your schema username, its database password, and a net service name, for example:

connection = cx_Oracle.connect(user="scott", password=userpwd,
                               dsn="cjdb1_high", encoding="UTF-8")

Once you have set optional Oracle environment variables used by your application, such as TNS_ADMIN, you can start your application.

If you need to create a new database schema so you do not login as the privileged ADMIN user, refer to the relevant Oracle Cloud documentation, for example see Create Database Users in the Oracle Autonomous Database manual.

Access Through a Proxy

If you are behind a firewall, you can tunnel TLS/SSL connections via a proxy using HTTPS_PROXY in the connect descriptor. Successful connection depends on specific proxy configurations. Oracle does not recommend doing this when performance is critical.

Edit sqlnet.ora and add a line:


Edit tnsnames.ora and add an HTTPS_PROXY proxy name and HTTPS_PROXY_PORT port to the connect descriptor address list of any service name you plan to use, for example:

cjdb1_high = (description=
    (protocol=tcps)(port=1522)(host= . . . )

Using the Easy Connect Syntax with Autonomous Database

When cx_Oracle is using Oracle Client libraries 19c or later, you can optionally use the Easy Connect syntax to connect to Oracle Autonomous Database.

The mapping from the cloud tnsnames.ora entries to an Easy Connect Plus string is:


For example, if your tnsnames.ora file had an entry:

cjjson_high = (description=(retry_count=20)(retry_delay=3)
    (security=(ssl_server_cert_dn=",OU=Oracle ADB SYDNEY,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

Then your applications can connect using the connection string:

dsn = "tcps://"
connection = cx_Oracle.connect(user="hr", password=userpwd, dsn=dsn,

The wallet_location parameter needs to be set to the directory containing the cwallet.sso file from the wallet ZIP. The other wallet files, including tnsnames.ora, are not needed when you use the Easy Connect Plus syntax.

You can add other Easy Connect parameters to the connection string, for example:

dsn = dsn + "&"

Connecting to Sharded Databases

Oracle Sharding can be used to horizontally partition data across independent databases. A database table can be split so each shard contains a table with the same columns but a different subset of rows. These tables are known as sharded tables. Sharding is configured in Oracle Database, see the Oracle Sharding manual. Sharding requires Oracle Database and Oracle Client libraries 12.2, or later.

The cx_Oracle.connect() and SessionPool.acquire() functions accept shardingkey and supershardingkey parameters that are a sequence of values used to route the connection directly to a given shard. A sharding key is always required. A super sharding key is additionally required when using composite sharding, which is when data has been partitioned by a list or range (the super sharding key), and then further partitioned by a sharding key.

When creating a connection pool, the cx_Oracle.SessionPool() attribute max_sessions_per_shard can be set. This is used to balance connections in the pool equally across shards. It requires Oracle Client libraries 18.3, or later.

Shard key values may be of type string (mapping to VARCHAR2 shard keys), number (NUMBER), bytes (RAW), or date (DATE). Multiple types may be used in each array. Sharding keys of TIMESTAMP type are not supported.

When connected to a shard, queries will only return data from that shard. For queries that need to access data from multiple shards, connections can be established to the coordinator shard catalog database. In this case, no shard key or super shard key is used.

As an example of direct connection, if sharding had been configured on a single VARCHAR2 column like:

  cust_id NUMBER,
  cust_name VARCHAR2(30),
  class VARCHAR2(10) NOT NULL,
  signup_date DATE,
  cust_code RAW(20),
  CONSTRAINT cust_name_pk PRIMARY KEY(cust_name))

then direct connection to a shard can be made by passing a single sharding key:

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               encoding="UTF-8", shardingkey=["SCOTT"])

Numbers keys can be used in a similar way:

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               encoding="UTF-8", shardingkey=[110])

When sharding by DATE, you can connect like:

import datetime

d = datetime.datetime(2014, 7, 3)

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               encoding="UTF-8", shardingkey=[d])

When sharding by RAW, you can connect like:

b = b'\x01\x04\x08';

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               encoding="UTF-8", shardingkey=[b])

Multiple keys can be specified, for example:

key_list = [70, "SCOTT", "gold", b'\x00\x01\x02']

connection = cx_Oracle.connect(user="hr", password=userpwd,
                               encoding="UTF-8", shardingkey=key_list)

A super sharding key example is:

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