4.1.1.Creating a Standalone Connection

Standalone connections are created by callingoracledb.connect().

A simple standalone connection example:

importoracledbimportgetpassuserpwd=getpass.getpass("Enter password: ")connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb")

You could alternatively read the password from an environment variable:

userpwd=os.environ.get("PYTHON_PASSWORD")connection=oracledb.connect(user="hr",password=userpwd,dsn="localhost/orclpdb")

Theoracledb.connect() method allows the database host name anddatabase service name to be passed as separate parameters. The databaselistener port can also be passed:

importosuserpwd=os.environ.get("PYTHON_PASSWORD")connection=oracledb.connect(user="hr",password=userpwd,host="localhost",port=1521,service_name="orclpdb")

A single, combined connection string can be passed toconnect() but thismay cause complications if the password contains “@” or “/” characters:

username="hr"userpwd=os.environ.get("PYTHON_PASSWORD")host="localhost"port=1521service_name="orclpdb"dsn=f'{username}/{userpwd}@{host}:{port}/{service_name}'connection=oracledb.connect(dsn)

If you like to encapsulate values, parameters can be passed using aConnectParams Object:

params=oracledb.ConnectParams(host="my_host",port=my_port,service_name="my_service_name")conn=oracledb.connect(user="my_user",password="my_password",params=params)

Some values such as the database host name can be specified asconnect()parameters, as part of thedsn connection string, and in theparamsobject. A final connection string is internally constructed from anydsn,individual parameters, andparams object values. The precedence is thatvalues in adsn parameter override values passed as individual parameters,which themselves override values set in theparams object.

withoracledb.connect(user="hr",password=userpwd,dsn="myhostname/orclpdb")asconnection:withconnection.cursor()ascursor:cursor.execute("insert into SomeTable values (:1)",("Some string"))connection.commit()

connection=oracledb.connect(user="hr",password=userpwd,dsn="localhost/orclpdb")# do something with the connection...# close the connectionconnection.close()

4.1.2.Common Connection Errors

Some of the common connection errors that you may encounter in thepython-oracledb’s default Thin mode are detailed below. Also seeError Handling in Thin and Thick Modes.

connection=oracledb.connect("hr",userpwd,"localhost/orclpdb")

TypeError:connect()takesfrom0to1positionalargumentsbut3weregiven

connection=oracledb.connect(user="hr",password=userpwd,dsn="localhost/orclpdb")

connection=oracledb.connect("hr/userpwd@localhost/orclpdb")

ORA-01017:invalidcredentialornotauthorized;logondenied

DPY-6001:cannotconnecttodatabase.Service"doesnotexist"isnotregisteredwiththelistenerathost"localhost"port1521.(SimilartoORA-12514)

4.2.1.Easy Connect Syntax for Connection Strings

AnEasy Connect string is often the simplestconnection string to use in the data source name parameterdsn ofconnection functions such asoracledb.connect(),oracledb.create_pool(),oracledb.connect_async(), andoracledb.create_pool_async().

Using Easy Connect strings means that an externaltnsnames.ora configuration file is not needed.

The Easy Connect syntax in python-oracledb is:

[[protocol:]//]host1{,host12}[:port1]{,host2:port2}{;host1{,host12}[:port1]}[/[service_name][:server][/instance_name]][?parameter_name=value{&parameter_name=value}]

See theDatabase Net Services Administrator’s Guideand the technical briefOracle Database Easy Connect Plus for more details.

For example, to connect to the Oracle Database serviceorclpdb that isrunning on the hostdbhost.example.com with the default OracleDatabase port 1521, use:

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb")

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

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com:1984/orclpdb")

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

Oracle Net Settings in Easy Connect Strings

The Easy Connect syntax allows someOracle Network and database configuration options to beset. This means that asqlnet.ora file is not needed forcommon connection scenarios.

For example, to set a connection timeout and keep-alive value:

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb?transport_connect_timeout=10&expire_time=2")

For more information, seeOracle Net Connect Descriptor and Easy Connect Keywords. Any Easy Connectparameters that are not known to python-oracledb are ignored and not passed tothe database.

Python-oracledb Settings in Easy Connect Strings

Many python-oracledb connection method API arguments can alternatively bepassed as Easy Connect parameters with a “pyo.” prefix. For example, to setthe statement cache size used by connections:

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb?pyo.stmtcachesize=50")

SeePython-oracledb Parameters Settable in Easy Connect Strings or Centralized Configuration Providers for the usable attributes.

4.2.2.Connect Descriptors

Connect Descriptors can be embedded directly in python-oracledb applications,or referenced via aTNS Alias.

An example of direct use is:

dsn="""(DESCRIPTION=             (FAILOVER=on)             (ADDRESS_LIST=               (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521))               (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521)))             (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))"""connection=oracledb.connect(user="hr",password=userpwd,dsn=dsn)

Theoracledb.ConnectParams() andConnectParams.get_connect_string() functions can be used to construct aconnect descriptor from the individual components, seeUsing the ConnectParams Builder Class.For example:

cp=oracledb.ConnectParams(host="dbhost.example.com",port=1521,service_name="orclpdb")dsn=cp.get_connect_string()print(dsn)

This prints:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb)))

Syntax is shown in theDatabase Net Services Reference.

AnyDESCRIPTION,CONNECT_DATA andSECURITY parameters of a fullconnect descriptor that are unrecognized by python-oracledb are passed to thedatabase unchanged.

4.2.3.TNS Aliases for Connection Strings

Connect Descriptors are commonly stored in atnsnames.ora file and associated with a TNS Alias. Thisalias can be used directly for the data source name parameterdsn oforacledb.connect(),oracledb.create_pool(),oracledb.connect_async(), andoracledb.create_pool_async().For example, given a file/opt/oracle/config/tnsnames.ora with thefollowing contents:

ORCLPDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb)))

Then you could connect by passing the TNS Alias “ORCLPDB” (case insensitive) asthedsn value:

connection=oracledb.connect(user="hr",password=userpwd,dsn="orclpdb",config_dir="/opt/oracle/config")

In python-oracledb Thick mode, the configuration directory can also be setduring library initialization:

oracledb.init_oracle_client(config_dir="/opt/oracle/config")connection=oracledb.connect(user="hr",password=userpwd,dsn="orclpdb")

More options for how python-oracledb locatestnsnames.orafiles are detailed inUsing Optional Oracle Configuration Files.

TNS Aliases may also be resolved byLDAP.

For more information about Net Service Names, seeDatabase Net ServicesReference.

4.2.4.LDAP URL Connection Strings

The python-oracledb connection string can be an LDAP URL like:

ldapurl="ldaps://ldapserver.example.com/cn=orcl,cn=OracleContext,dc=example,dc=com"connection=oracledb.connect(user="scott",password=pw,dsn=ldapurl)

This syntax removes the need for external LDAP andsqlnet.ora configurationfiles. See the technical briefOracle Client 23ai LDAP URL Syntax.

In python-oracledb Thin mode, an additionalconnection protocol hookfunction is required to handle this connectionprotocol, seeLDAP Directory Naming. A connection protocol hook function isalso required in python-oracledb Thick mode ifdefaults.thick_mode_dsn_passthrough isFalse.

To use LDAP URLs in python-oracledb Thick mode applications whendefaults.thick_mode_dsn_passthrough isTrue, the Oracle Clientlibraries must be 23.4, or later.

4.2.5.Centralized Configuration Provider URL Connection Strings

ACentralized Configuration Provider URLconnection string allows python-oracledb configuration information to be storedcentrally in OCI Object Storage, in Azure App Configuration, or in a localfile. Given a provider URL, python-oracledb will access the information storedin the configuration provider and use it to connect to Oracle Database.

The database connect descriptor and any database credentials stored in aconfiguration provider will be used by any language driver that accesses theconfiguration. Other driver-specific sections can exist. Python-oracledb willtake settings that are in a section with the prefix “pyo”, and will ignoreother sections.

For example, to use connection configuration stored in a local file/opt/oracle/my-config.json:

{"connect_descriptor":"localhost/orclpdb","pyo":{"min":5,"max":10,"increment":2"stmtcachesize":4}}

You could use this to create a connection pool by specifying thedsnconnection string parameter as:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="config-file:///opt/oracle/my-config.json")

The pool will be created using the pool settings from the configuration.

The Centralized Configuration Provider URL must begin with“config-<configuration-provider>://” where the configuration-provider value canbe set toociobject,azure, orfile, depending on the location of yourconfiguration information.

SeeCentralized Configuration Providers for more information, particularly regardingusing python-oracledb Thick mode.

The valid keys for the “pyo” object are shown inPython-oracledb Parameters Settable in Easy Connect Strings or Centralized Configuration Providers.

4.2.6.JDBC and Oracle SQL Developer Connection Strings

The python-oracledb connection string syntax is different from Java JDBC and thecommon Oracle SQL Developer syntax. If these JDBC connection strings referencea service name like:

jdbc:oracle:thin:@hostname:port/service_name

For example:

jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb

then use Oracle’s Easy Connect syntax in python-oracledb:

connection=oracledb.connect(user="hr",password=userpwd,dsn="dbhost.example.com:1521/orclpdb")

You may need to remove JDBC-specific parameters from the connection string anduse python-oracledb alternatives.

If a JDBC connection string uses an old-style Oracle Database SID “systemidentifier”, and the database does not have a service name:

jdbc:oracle:thin:@hostname:port:sid

For example:

jdbc:oracle:thin:@dbhost.example.com:1521:orcl

then connect by using thesid parameter:

connection=oracledb.connect(user="hr",password=userpwd,host="dbhost.example.com",port=1521,sid="orcl")

Alternatively, create atnsnames.ora entry (seeOptional Oracle Net Configuration Files), forexample:

finance=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))

This can be referenced in python-oracledb:

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

4.2.7.Oracle Net Connect Descriptor and Easy Connect Keywords

Easy Connect syntax is described inEasy Connect Syntax for Connection Strings.

Connect Descriptor keywords are shown in theDatabase Net Services Reference.

Notes on specific keywords

ThePOOL_CONNECTION_CLASS orPOOL_PURITY values will only work whenconnected to Oracle Database 21c, or later. Note ifPOOL_PURITY=SELF isused in a connect string, then python-oracledb Thick mode applications willignore the action to drop the session when attempting to remove an unusableconnections from a pool in some uncommon error cases. It is recommended toavoid usingPOOL_PURITY=SELF in a connect string with python-oracledb Thickmode. Instead, code python-oracledb Thick mode applications to explicitlyspecify the purity and connection class as attributes.

TheENABLE=BROKEN connect descriptor option is not supported bypython-oracledb Thin mode. UseEXPIRE_TIME instead.

If a name is given as a connect string, then python-oracledb will consider itas a Net Service Name and not as the minimal Easy Connect string of a hostname.The given connect string will be looked up in atnsnames.ora file. If supporting a bare name as a hostname is important toyou in python-oracledb, then you can alter the connection string to include aprotocol such astcp://hostname, or a port number such ashostname:1521.

In python-oracledb Thick mode, whendefaults.thick_mode_dsn_passthroughisFalse, anyDESCRIPTION,CONNECT_DATA andSECURITY parametersof a full connect descriptor that are unrecognized by python-oracledb arepassed to the database unchanged. Any Easy Connect parameters that are notknown to python-oracledb are discarded and not passed to the database.

4.2.8.Python-oracledb Parameters Settable in Easy Connect Strings or Centralized Configuration Providers

Some python-oracledb connection and pool creation parameters can be set inEasy Connect strings or via aCentralizedConfiguration Provider. This is an alternative topassing explicit arguments tooracledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async(). This allows application behavior to bechanged without needing application code to be updated.

The parameters are shown below inthis table. Parameters have a “pyo.” prefix or are under a“pyo” key. Each of these parameters that is defined in an Easy Connect stringor via a Centralized Configuration Provider will take precedence over the valuepassed as the equivalent python-oracledb API parameter.

Parameters that apply topool creation will be ignored ifthey are used in the context ofstandalone connections. Parameters with unknown names will be ignored in bothcases.

Python-oracledb Parameters in Easy Connect Strings

The Easy Connect parameter names are similar to the python-oracledb methodargument names, but have a “pyo.” prefix. For example:

cs="host.example.com:1522/orclpdb?pyo.stmtcachesize=30&pyo.mode=SYSDBA"connection=oracledb.connect(user="hr",password=userpwd,dsn=cs)

is the same as:

cs="host.example.com:1522/orclpdb"connection=oracledb.connect(user="hr",password=userpwd,dsn=cs,stmtcachesize=30,mode=oracledb.AuthMode.SYSDBA)

If a parameter is specified multiple times in an Easy Connect string, then thelast value of that parameter is used. For example, in“localhost/orclpdb?pyo.sdu=10&pyo.sdu=20” the SDU is set to 20.

Note some Oracle Net parameters can also be prefixed with “pyo.”.

Parameters with the prefix “pyo.” can only be used in Easy Connect strings andnot inConnect Descriptors.

Python-oracledb Parameters in Configuration Providers

With theFile Centralized Configuration Provider orOCI Object Storage Centralized Configuration Provider, the settable python-oracledb driver attributes shouldbe in the JSON file under the key “pyo”. An example is:

{"connect_descriptor":"localhost/orclpdb","pyo":{"min":5,"max":10,"increment":2"stmtcachesize":4}}

WithAzure App Configuration, values are setusing a key such as “<prefix>/pyo/<key name>”. This is similar to howOracleCall Interface settings use the key “<prefix>/oci/<key name>” as shown inOracle Net Service Administrator’s Guide.

Parameter Names

When used in Easy Connect Strings, the parameter names should be prefixed with“pyo.”. When used in a Centralized Configuration Provider, the parameternames are used to form the key names under a parent “pyo” key or with a “pyo/”prefix. The names are case insensitive.

4.3.1.Using a File Centralized Configuration Provider

The File Centralized Configuration Provider enables the storage and managementof Oracle Database connection information using local files.

To use a File Centralized Configuration Provider, you must:

1. Store the connection information in a JSON file on your local file system.

2. Set the path to the file in thedsn parameter of connection and poolcreation methods.

File Centralized Configuration Provider JSON File Syntax

The configuration file must contain at least aconnect_descriptor key tospecify the database connection string. Optionally, you can store the databaseuser name, password, a cache time, andpython-oracledb settings. The keys that can be stored in the file are:

See theOracle Net Service Administrator’s Guide formore information on the generic provider sub-objects usable in JSON files.

Multiple configurations can be defined by specifying the above keys underuser-chosen, top-level keys, see the example further below.

File Centralized Configuration Provider DSN Syntax

To use a file provider, specify thedsn parameter oforacledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() usingthe following format:

config-file://<file-path-and-name>[?key=<key_name>]

The elements of thedsn parameter are detailed in the table below.

File Configuration Provider Examples

An example of File Configuration Provider file syntax is:

{"user":"scott","password":{"type":"base64","value":"dGlnZXI="},"connect_descriptor":"dbhost.example.com:1522/orclpdb","pyo":{"stmtcachesize":30,"min":2,"max":10}}

This encodes the password as base64. SeeUsing an OCI Object Storage Centralized Configuration Provider forother password examples. Plaintext passwords are not supported.

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

If you have this configuration file in/opt/oracle/my-config1.json, youcould use it like:

connection=oracledb.connect(dsn="config-file:///opt/oracle/my-config1.json")

Multiple configurations can be defined by specifying user-chosen top-levelkeys:

{"production":{"connect_descriptor":"localhost/orclpdb"},"testing":{"connect_descriptor":"localhost/orclpdb","user":"scott","password":{"type":"base64","value":"dGlnZXI="}}}

If you have this configuration file in/opt/oracle/my-config2.json, youcould use it like:

connection=oracledb.connect(user="hr",password=userpwd,dsn="config-file:///opt/oracle/my-config2.json?key=production")

4.3.2.Using an OCI Object Storage Centralized Configuration Provider

The Oracle Cloud Infrastructure (OCI)Object Storage configuration provider enables the storage and management of OracleDatabase connection information as JSON inOCI Object Storage.

To use an OCI Object Storage Centralized Configuration Provider, you must:

1. Upload a JSON file that contains the connection information into an OCIObject Storage Bucket. SeeUploading an Object Storage Object to a Bucket and theOracle Net ServiceAdministrator’s Guide for the steps.SeeOCI Object Storage Centralized Configuration Provider Parameters for the configuration information that can be added.

2. Install the PythonOCI module, seeInstall Modules for the OCI Object Storage Centralized Configuration Provider.

3. Import theoracledb.plugins.oci_config_providerplugin in your application.

4. Use an OCI Object Storage connection string URLin thedsn parameter of connection and pool creation methods.

OCI Object Storage Centralized Configuration Provider JSON File Syntax

The stored JSON configuration file must contain aconnect_descriptor key.Optionally, you can specify the database user name, password, a cache time, andpython-oracledb attributes. The database password can also be stored securelyusingOCI Vault orAzure Key Vault. Thekeys that can be in the JSON file are listed below.

OCI Object Storage Centralized Configuration Provider DSN Syntax

Thedsn parameter fororacledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() calls should use a connection string URLin the format:

config-ociobject:<objectstorage-name>/n/{namespaceName}/b/{bucketName}/o/<objectName>[/c/<networkServiceName>][?<option1>=<value1>&<option2>=<value2>...]

The elements of the connection string are detailed in the table below.

OCI Object Storage Centralized Configuration Provider Examples

An example of OCI Object Centralized Storage Configuration Provider JSON filesyntax is:

{"user":"scott","password":{"type":"ocivault","value":"oci.vaultsecret.my-secret-id""authentication":{"method":"OCI_INSTANCE_PRINCIPAL"}},"connect_descriptor":"dbhost.example.com:1522/orclpdb","pyo":{"stmtcachesize":30,"min":2,"max":10}}

Passwords can optionally be stored using the Azure Key Vault. To do this,you must import theoracledb.plugins.azure_config_provider python-oracledb plugin in your application and you mustdefine the Azure Key Vault credentials in thepassword key. In this, theazure_client_id andazure_tenant_id must be specified. Also, eitherazure_client_secret orazure_client_certificate_path should bespecified. For example:

"password":{"type":"azurevault","value":"<Azure Key Vault URI>","authentication":{"azure_tenant_id":"<tenant_id>","azure_client_id":"<client_id>","azure_client_secret":"<secret value>"}}

Or:

"password":{"type":"azurevault","value":"<Azure Key Vault URI>","authentication":{"azure_tenant_id":"<tenant_id>","azure_client_id":"<client_id>","azure_client_certificate_path":"<azure_client_certificate_path>"}}

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

An example of a connection string for the OCI Object Centralized Storageconfiguration provider is:

configociurl="config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?authentication=oci_default&oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"

To create astandalone connection you could usethis like:

importoracledb.plugins.oci_config_providerconfigociurl="config-ociobject://abc.oraclecloud.com/n/abcnamespace/b/abcbucket/o/abcobject?authentication=oci_default&oci_tenancy=abc123&oci_user=ociuser1&oci_fingerprint=ab:14:ba:13&oci_key_file=ociabc/ocikeyabc.pem"connection=oracledb.connect(dsn=configociurl)

The configuration can also be used to create aconnection pool, for example:

pool=oracledb.create_pool(dsn=configociurl)

4.3.3.Using an Azure App Centralized Configuration Provider

Azure App Configuration is a cloud-based service provided by MicrosoftAzure. It can be used for storage and management of Oracle Database connectioninformation as key-value pairs.

To use python-oracledb with Azure App Configuration, you must:

1. Save your configuration information in your Azure App ConfigurationProvider. SeeAzure App Centralized Configuration Provider Parameters.

2. Install the Azure App modules, seeInstall Modules for the Azure App Centralized Configuration Provider.

3. Import theoracledb.plugins.azure_config_providerplugin in your application.

4. Use an Azure App Configuration connection string URL in thedsn parameter of connection and pool creationmethods.

Azure App Centralized Configuration Provider Parameters

Key-value pairs for stored connection information can be added using theConfiguration explorer page of your Azure App Configuration. SeeCreate akey-value in Azure App Configuration for more information. Alternatively,they can be set by makingREST calls. Also see theOracle Net ServiceAdministrator’s Guide.

You can organize the key-value pairs under a prefix based on yourapplication’s needs. For example, if you have two applications, Sales andHuman Resources, then you can store the relevant configuration informationunder the prefixsales and the prefixhr.

The key-value pairs must contain the keyconnect_descriptor which specifiesthe database connection string. This can be set using a prefix as“<prefix>/connect_descriptor”, for example,sales/connect_descriptor.

You can additionally store the database user name using a key such as“<prefix>/user”, and store the password using “<prefix>/password”. For example,sales/user andsales/password. The database password can also be storedsecurely usingAzure Key Vault. A cache time can optionally be storedusing “<prefix>/config_time_to_live”. For example,sales/60000. SeeCaching Configuration Information.

Optional python-oracledb settings can be set using a key such as“<prefix>/pyo/<key name>”, for examplesales/pyo/min. This is similar to howOracle Call Interface settings use keys like “<prefix>/oci/<key name>” as shown inOracle Net Service Administrator’s Guide.

The keys that can be added in Azure App Configuration are listed below:

Azure App Centralized Configuration Provider DSN Syntax

You must define a connection string URL in a specific format in thedsnparameter oforacledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() toaccess the information stored in Azure App Configuration. The syntax is:

config-azure://<appconfigname>[?key=<prefix>&label=<value>&<option1>=<value1>&<option2>=<value2>...]

The elements of the connection string are detailed in the table below.

Azure App Centralized Configuration Examples

The following table shows sample configuration information defined using theConfiguration explorer page of your Azure App Configuration provider. Theexample uses the prefixtest/.

Note that python-oracledb caches configurations by default, seeCaching Configuration Information.

An example of a connection string for the Azure App Configuration provider is:

configazureurl="config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"

An example using astandalone connection is:

importoracledb.plugins.azure_config_providerconfigazureurl="config-azure://aznetnamingappconfig.azconfig.io/?key=test/&azure_client_id=123-456&azure_client_secret=MYSECRET&azure_tenant_id=789-123"oracledb.connect(dsn=configazureurl)

The configuration can also be used to create aconnection pool, for example:

oracledb.create_pool(dsn=configazureurl)

4.3.4.Caching Configuration Information

Python-oracledb caches configurations obtained from Centralized ConfigurationProviders to reduce access overheads.

You can use theconfig_time_to_live configuration key to specify the numberof seconds that python-oracledb should keep the information cached. The defaulttime is 86,400 seconds (24 hours).

Whenconfig_time_to_live is reached, the configuration is considered to be“softly expired” and subsequent python-oracledb connections will attempt toobtain the configuration again from the configuration provider. If it cannot beretrieved, python-oracledb will continue to use the previous configuration forup toconfig_time_to_live_grace_period seconds which defaults to 1,800seconds (30 minutes). After this grace period the cached configuration fullyexpires. Future connection attempts will try to retrieve the configuration fromthe provider but will fail if the new configuration cannot be obtained.

An example of changing the cache time to 12 hours with an additional grace timeof 10 minutes for the File or OCI Object Storage Centralized ConfigurationProviders is:

{"connect_descriptor":"dbhost.example.com:1522/orclpdb","config_time_to_live":43200,"config_time_to_live_grace_period":600,"pyo":{"stmtcachesize":30,"min":2,"max":10}}

4.5.1.Using Protocol Hook Functions

Theoracledb.register_protocol() method registers a user protocol hookfunction that will be called internally by python-oracledb Thin mode prior toconnection or pool creation. The hook function will be invoked whenoracledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() arecalled with adsn parameter value prefixed with a specified protocol. Yourhook function is expected to construct valid connection details, whichpython-oracledb will use to complete the connection or pool creation.

You can also make use of a protocol hook function in python-oracledb Thick modeconnection calls by settingdefaults.thick_mode_dsn_passthrough toFalse. Alternatively useConnectParams.parse_connect_string(), seeUsing the ConnectParams Builder Class.

For example, the following hook function handles connection strings prefixedwith thetcp:// protocol. Whenoracledb.connect() is called, thesample hook is invoked internally. It prints the parameters, and sets theconnection information in theparams parameter (without passing thetcp:// prefix toparse_connect_string() otherwiserecursion would occur). This modified ConnectParams object is used bypython-oracledb to establish the database connection:

defmyprotocolhook(protocol,arg,params):print(f"In myprotocolhook: protocol={protocol} arg={arg}")params.parse_connect_string(arg)oracledb.register_protocol("tcp",myprotocolhook)connection=oracledb.connect(user="scott",password=userpwd,dsn="tcp://localhost/orclpdb")withconnection.cursor()ascursor:for(r,)incursor.execute("select user from dual"):print(r)

The output would be:

Inmyprotocolhook:protocol=tcparg=localhost/orclpdbSCOTT

Theparamsattributes can be set withConnectParams.parse_connect_string(), as shown, or by usingConnectParams.set().

SeeLDAP Directory Naming for a fuller example.

Internal protocol hook functions for the “tcp” and “tcps” protocols arepre-registered but can be overridden, if needed. If any other protocol has notbeen registered, then connecting will result in an error.

Callingregister_protocol() with thehook_functionparameter set to None will result in a previously registered user functionbeing removed and the default behavior restored.

Connection Hooks and parse_connect_string()

A registered user protocol hook function will also be invoked inpython-oracledb Thin or Thick modes whenConnectParams.parse_connect_string() is called with aconnect_string parameter beginning with the registered protocol. The hookfunctionparams value will be the invoking ConnectParams instance that youcan update usingConnectParams.set() orConnectParams.parse_connect_string().

For example, with the hookmyprotocolhook shown previously, then the code:

cp=oracledb.ConnectParams()cp.set(port=1234)print(f"host is{cp.host}, port is{cp.port}, service name is{cp.service_name}")cp.parse_connect_string("tcp://localhost/orclpdb")print(f"host is{cp.host}, port is{cp.port}, service name is{cp.service_name}")

prints:

hostisNone,portis1234,servicenameisNoneInmyprotocolhook:protocol=tcparg=localhost/orclpdbhostislocalhost,portis1234,servicenameisorclpdb

If you have an application that can run in either python-oracledb Thin or Thickmodes, and you want a registered connection protocol hook function to be usedin both modes, your connection code can be like:

dsn="tcp://localhost/orclpdb"cp=oracledb.ConnectParams()cp.parse_connect_string(dsn)connection=oracledb.connect(user="hr",password=userpwd,params=cp)

4.5.2.Using Password Hook Functions

Theoracledb.register_password_type() method registers a user passwordhook function that will be called internally by python-oracledb prior toconnection or pool creation whenoracledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() are called. If thepassword,newpassword, orwallet_password parameters to those methods are adictionary containing the key “type”, then the registered user password hookfunction for the specific type will be invoked. Your hook function is expectedto accept the dictionary and return the actual password string.

Below is an example of a password hook function that handles passwords of typebase64 stored in a dict like “dict(type=’base64’, value=’dGlnZXI=’)”. Notethis specific hook function is already included and registered inpython-oracledb:

defmypasswordhook(args):returnbase64.b64decode(args["value"].encode()).decode()oracledb.register_password_type("base64",mypasswordhook)

Whenoracledb.connect() is called as shown below, the sample hook isinvoked internally. It decodes the base64-encoded string in the key “value” andreturns the password which is then used by python-oracledb to establish aconnection to the database:

connection=oracledb.connect(user="scott",password=dict(type="base64",value="dGlnZXI="),dsn="localhost/orclpdb")

Callingregister_password_type() with thehook_functionparameter set toNone will result in a previously registered user functionbeing removed.

4.5.3.Using Parameter Hook Functions

Theoracledb.register_params_hook() method registers a user parameterhook function that will be called internally by python-oracledb prior toconnection or pool creation whenoracledb.connect(),oracledb.create_pool(),oracledb.connect_async(), ororacledb.create_pool_async() are called. Your parameter hook functionshould accept a copy of the parameters that will be used to create the pool orstandalone connections. The function can access and modify them in any waynecessary to allow python-oracledb to subsequently complete the connection orpool creation request.

Pre-supplied python-oracledb plugins such as theOCI Cloud NativeAuthentication Plugin (oci_tokens) make use oforacledb.register_params_hook(). This plugin uses the information foundin a connection method’sextra_auth_params parameter and modifies theaccess_token parameter with a function that will acquire the authenticationtoken needed to complete a connection. Refer to the complete pluginimplementation inoci_tokens.py. The key code section showingregistering of a parameter hook function is:

defoci_token_hook(params:oracledb.ConnectParams):ifparams.extra_auth_paramsisnotNone:deftoken_callback(refresh):returngenerate_token(params.extra_auth_params,refresh)params.set(access_token=token_callback)oracledb.register_params_hook(oci_token_hook)

Your code might then try to connect like:

token_based_auth={"auth_type":"SimpleAuthentication","user":<user>,"key_file":<key_file>,"fingerprint":<fingerprint>,"tenancy":<tenancy>,"region":<region>,"profile":<profile>}connection=oracledb.connect(dsn=mydb_low,extra_auth_params=token_based_auth)

To unregister a user function that was earlier registered, you can useoracledb.unregister_params_hook().

If you have registered user hook methods withoracledb.register_protocol() andoracledb.register_params_hook(), then the method registered withoracledb.register_protocol() is invoked first during connection or poolcreation calls. If you callConnectParams.parse_connect_string(), theregistered protocol hook method will be called but the parameter hook will notbe.

4.8.1.Driver Connection Pooling

Python-oracledb’s driver connection pooling lets applications create andmaintain a pool of open connections to the database. Connection pooling isavailable in both Thin andThick modes. Connectionpooling is important for performance and scalability when applications need tohandle a large number of users who do database work for short periods of timebut have relatively long periods when the connections are not needed. The highavailability features of pools also make small pools useful for applicationsthat want a few connections available for infrequent use and requires them tobe immediately usable when acquired. Applications that would benefit fromconnection pooling but are too difficult to modify from the use ofstandalone connections can take advantage ofImplicit Connection Pooling.

In python-oracledb Thick mode, the pool implementation uses Oracle’ssessionpool technology which supports additionalOracle Database features, for example some advancedhigh availability features.

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",min=1,max=5,increment=1)

connection=pool.acquire()

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",min=2,max=5,increment=1,getmode=oracledb.POOL_GETMODE_NOWAIT)

withpool.acquire()asconnection:withconnection.cursor()ascursor:forresultincursor.execute("select * from mytab"):print(result)

withpool.acquire()asconnection:...pool.drop(connection)

pool.close()

pool.close(force=True)

4.8.2.Using the Connection Pool Cache

When your application architecture makes it difficult to pass aConnectionPool object between your code layers, you can usethe python-oracledb connection pool cache. This lets you store and retrievepools by name.

Adding a pool to the python-oracledb connection pool cache

To use the python-oracledb pool cache, specify thepool_alias parameterwhen you create a pool during application initialization. Its value should be auser-chosen string. For example:

importoracledbNAME="my_pool"oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",pool_alias=NAME)

This creates a pool and stores it in the cache under the name “my_pool”. Theapplication does not need to store or manage the reference to the pool so thecreate_pool() return value is not saved.

If a pool already exists with the name “my_pool”, the following error willbe raised:

DPY-2055:connectionpoolwithname"my_pool"alreadyexists

Getting a connection from a cached pool

Applications can get a connection from a cached pool by passing its namedirectly tooracledb.connect():

importoracledbNAME="my_pool"connection=oracledb.connect(pool_alias=NAME)

This is equivalent to callingConnectionPool.acquire(). You can passadditional parameters toconnect() that are allowed foracquire(). For example, with aheterogeneous pool you can pass the username and password:

importoracledbNAME="my_pool"connection=oracledb.connect(pool_alias=NAME,user="toto",password=pw)

If there is no pool namedmy_pool in the cache, you will get the followingerror:

DPY-2054:connectionpoolwithname"my_pool"doesnotexist

You cannot passpool_alias and the deprecatedpool parameter togethertooracledb.connect() ororacledb.connect_async(). If you do,the following error is raised:

DPY-2014:"pool_alias"and"pool"cannotbespecifiedtogether

Getting a pool from the connection pool cache

You can useoracledb.get_pool() to retrieve a pool and then access itdirectly:

importoracledbNAME="my_pool"pool=oracledb.get_pool(NAME)connection=pool.acquire()

This allows any connection poolmethod orattribute from a cached pool to be used, as normal.

If there is no pool namedmy_pool in the cache, thenget_pool() will return None.

Removing a pool from the cache

A pool is automatically removed from the cache when the pool is closed:

importoracledbNAME="my_pool"pool=oracledb.get_pool(NAME)pool.close()

4.8.3.Connection Pool Sizing

The Oracle Real-World Performance Group’s recommendation is to use fixed sizeconnection pools. The values ofmin andmax should be the same. Whenusing older versions of Oracle Client libraries theincrement parameterwill need to be zero (which is internally treated as a value of one), butotherwise you may prefer a larger size since this will affect how theconnection pool is re-established after, for example, a network dropoutinvalidates all connections.

Fixed size pools avoid connection storms on the database which can decreasethroughput. SeeGuideline for Preventing Connection Storms: Use Static Pools, which contains more details about sizing of pools.Having a fixed size will also guarantee that the database can handle the upperpool size. For example, if a dynamically sized pool needs to grow but thedatabase resources are limited, thenConnectionPool.acquire() mayreturn errors such asORA-28547. With a fixed pool size, this class of error will occur when thepool is created, allowing you to change the pool size or reconfigure thedatabase before users access the application. With a dynamically growing pool,the error may occur much later while the application is in use.

The Real-World Performance Group also recommends keeping pool sizes smallbecause they often can perform better than larger pools. The pool attributesshould be adjusted to handle the desired workload within the bounds ofavailable resources in python-oracledb and the database.

4.8.4.Pool Connection Health

BeforeConnectionPool.acquire() returns, python-oracledb does alightweight check similar toConnection.is_healthy() to see if thenetwork transport for the selected connection is still open. If it is not,thenacquire() will clean up the connection and returna different one.

This check will not detect cases such as where the database session has beenterminated by the DBA, or reached a database resource manager quota limit. Tohelp in those cases,acquire() will also do a fullround-trip database ping similar toConnection.ping() when it is about to return a connection that was idlein the pool (i.e. not acquired by the application) forConnectionPool.ping_interval seconds. If the ping fails, theconnection will be discarded and another one obtained beforeacquire() returns to the application. Theping_timeout parameter tooracledb.create_pool() limits the amountof time that any internal ping is allowed to take. If it is exceeded, perhapsdue to a network hang, the connection is considered unusable and a differentconnection is returned to the application.

Because this full ping is time based and may not occur for eachacquire(), the application may still get an unusableconnection. Also, network timeouts and session termination may occur betweenthe calls toacquire() andCursor.execute().To handle these cases, applications need to check for errors after eachexecute() and make application-specific decisions aboutretrying work if there was a connection failure. When using python-oracledb inThick mode, Oracle Database features likeApplication Continuity can do this automatically in some cases.

You can explicitly initiate a full round-trip ping at any time withConnection.ping() to check connection liveness but the overuse willimpact performance and scalability. To avoid pings hanging due to networkerrors, useConnection.call_timeout to limit the amount of timeping() is allowed to take.

TheConnection.is_healthy() method is an alternative toConnection.ping(). It has lower overheads and may suit some uses, butit does not perform a full connection check.

If thegetmode parameter inoracledb.create_pool() is set tooracledb.POOL_GETMODE_TIMEDWAIT, then the maximum amount of time anacquire() call will wait to get a connection from thepool is limited by the value of theConnectionPool.wait_timeoutparameter. A call that cannot be immediately satisfied will wait no longerthanwait_timeout regardless of the value ofping_timeout.

Connection pool health can be impacted byfirewalls,resource managers or user profileIDLE_TIME values. For best efficiency, ensure these do notexpire idle sessions since this will require connections to be recreated whichwill impact performance and scalability.

A pool’s internal connection re-establishment after lightweight and full pingscan mask performance-impacting configuration issues such as firewallsterminating connections. You should monitorAWRreports for an unexpectedly large connection rate.

4.8.5.Connection Pool Reconfiguration

Some pool settings can be changed dynamically withConnectionPool.reconfigure(). This allows the pool size and otherattributes to be changed during application runtime without needing to restartthe 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 otherparameters is done sequentially. If an error such as an invalid value occurswhen changing one attribute, then an exception will be generated but any alreadychanged attributes will retain their new values.

During reconfiguration of a pool’s size, the behavior ofConnectionPool.acquire() depends on the pool creationgetmode valuein effect whenacquire() is called, seeConnectionPool.reconfigure(). Closing connections or closing the poolwill wait until after pool reconfiguration is complete.

Callingreconfigure() is the only way to change a pool’smin,maxandincrement values. Other attributes such aswait_timeout can be passed toreconfigure() or theycan be set directly, for example:

pool.wait_timeout=1000

4.8.6.Session Callbacks for Setting Pooled Connection State

Applications can set “session” state in each connection. Examples of sessionstate areNLS globalization settings fromALTERSESSION statements. Pooled connections will retain their session state afterthey have been released back to the pool. However, because pools can grow orconnections in the pool can be recreated, there is no guarantee a subsequentacquire() call will return a database connection thathas any particular state.

Thecreate_pool() parametersession_callback enablesefficient setting of session state so that connections have a known sessionstate, without requiring that state to be explicitly set after everyacquire() call. The callback is internally invokedwhenacquire() is called and runs first.

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

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

Connection tagging and PL/SQL callbacks are only available in python-oracledbThick mode. Python callbacks can be used in python-oracledb Thin and Thickmodes.

There are three common scenarios forsession_callback:

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

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

• WithDatabase Resident Connection Pooling (DRCP), use a PL/SQL callback with tagging.

# Set the NLS_DATE_FORMAT for a sessiondefinit_session(connection,requested_tag):withconnection.cursor()ascursor:cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI'")# Create the pool with session callback definedpool=oracledb.create_pool(user="hr",password=userpwd,dsn="localhost/orclpdb",session_callback=init_session)# Acquire a connection from the pool (will always have the new date format)connection=pool.acquire()

cursor.execute("""        begin            execute immediate                    'alter session set nls_date_format = ''YYYY-MM-DD''                                       nls_language = AMERICAN';            -- other SQL statements could be put here        end;""")

definit_session(connection,requested_tag):ifrequested_tag=="NLS_DATE_FORMAT=SIMPLE":sql="ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'"elifrequested_tag=="NLS_DATE_FORMAT=FULL":sql="ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'"cursor=connection.cursor()cursor.execute(sql)connection.tag=requested_tagpool=oracledb.create_pool(user="hr",password=userpwd,dsn="orclpdb",session_callback=init_session)# Two connections with different session state:connection1=pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE")connection2=pool.acquire(tag="NLS_DATE_FORMAT=FULL")

PROCEDUREmyPlsqlCallback(requestedTagINVARCHAR2,actualTagINVARCHAR2);

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",session_callback="MyPlsqlCallback")connection=pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE",# DRCP options, if you are using DRCPcclass='MYCLASS',purity=oracledb.PURITY_SELF)

4.8.7.Heterogeneous and Homogeneous Connection Pools

Homogeneous Pools

By default, connection pools are ‘homogeneous’, meaning that all connectionsuse the same database credentials. Both python-oracledb Thin andThick modes support homogeneous pools.

Heterogeneous Pools

The python-oracledb Thick mode additionally supports Heterogeneous pools,allowing different user names and passwords to be passed to eachacquire() call.

To create an heterogeneous pool, set thecreate_pool()parameterhomogeneous to False:

pool=oracledb.create_pool(dsn="dbhost.example.com/orclpdb",homogeneous=False)connection=pool.acquire(user="hr",password=userpwd)

4.8.8.Using the PoolParams Builder Class

ThePoolParams class allows you to define connection andpool parameters in a single place. Theoracledb.PoolParams() functionreturns aPoolParams object. This is a subclass of theConnectParams class (seeUsing the ConnectParams Builder Class) withadditional pool-specific attributes such as the maximum pool size. APoolParams object can be passed tooracledb.create_pool(). Forexample:

pp=oracledb.PoolParams(min=1,max=2,increment=1)pool=oracledb.create_pool(user="hr",password=userpw,dsn="dbhost.example.com/orclpdb",params=pp)

The use of the PoolParams class is optional because you can pass the sameparameters directly tocreate_pool(). For example, the codeabove is equivalent to:

pool=oracledb.create_pool(user="hr",password=userpw,dsn="dbhost.example.com/orclpdb",min=1,max=2,increment=1)

Some values such as the database host name can be specified asoracledb.create_pool() parameters, as part of thedsn connectionstring, and in theparams object. A final connection string is internallyconstructed from anydsn, individual parameters, andparams objectvalues. The precedence is that values in adsn parameter override valuespassed as individual parameters, which themselves override values set in theparams object.

Most PoolParams arguments are gettable as properties. They may be setindividually using theset() method:

pp=oracledb.PoolParams()pp.set(min=5)print(pp.min)# 5

4.9.1.Enabling DRCP in Oracle Database

Oracle Database versions prior to 21c can have a single DRCP connection pool.From Oracle Database 21c, each pluggable database can optionally have its ownpool, or can use the container level pool. From Oracle Database 23ai, you cancreate multiple pools at the pluggable, or container, database level. Thismulti-pool feature is useful where different applications connect to the samedatabase, but there is a concern that one application’s use of the pool mayimpact other applications. If this is not the case, a single pool may allowbest resource sharing on the database host.

Note that DRCP is already enabled in Oracle Autonomous Database and poolmanagement is different to the steps below.

In the basic scenario, DRCP pools can be configured and administered by a DBAusing theDBMS_CONNECTION_POOL package:

EXECUTEDBMS_CONNECTION_POOL.CONFIGURE_POOL(pool_name=>'SYS_DEFAULT_CONNECTION_POOL',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 methodDBMS_CONNECTION_POOL.ALTER_PARAM() canset a single parameter:

EXECUTEDBMS_CONNECTION_POOL.ALTER_PARAM(pool_name=>'SYS_DEFAULT_CONNECTION_POOL',param_name=>'MAX_THINK_TIME',param_value=>'1200')

Theinactivity_timeout setting terminates idle pooled servers, helpingoptimize database resources. To avoid pooled servers permanently being heldonto by a selfish Python script, themax_think_time parameter can be set.The parametersnum_cbrok andmaxconn_cbrok can be used to distributethe persistent connections from the clients across multiple brokers. This maybe needed in cases where the operating system per-process descriptor limit issmall. Some customers have found that having several connection brokersimproves performance. Themax_use_session andmax_lifetime_sessionparameters help protect against any unforeseen problems affecting serverprocesses. The default values will be suitable for most users. See theOracle DRCP documentation for details on parameters.

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

There is aDBMS_CONNECTION_POOL.RESTORE_DEFAULTS() procedure toreset all values.

When DRCP is used withOracle RAC, each databaseinstance has its own connection broker and pool of servers. Each pool has theidentical configuration. For example, all pools start withminsize serverprocesses. A single DBMS_CONNECTION_POOL command will alter the pool of eachinstance at the same time. The pool needs to be started before connectionrequests begin. The command below does this by bringing up the broker, whichregisters itself with the database listener:

EXECUTEDBMS_CONNECTION_POOL.START_POOL()

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

EXECUTEDBMS_CONNECTION_POOL.STOP_POOL()

Oracle Database 23ai allows aDRAINTIME argument to be passed toSTOP_POOL(), indicating that the pool will only be closed after thespecified time. This allows in-progress application work to continue. Adraintime value of 0 can be used to immediately close the pool. See thedatabase documentation onDBMS_CONNECTION_POOL.STOP_POOL().

In older database versions, the pool cannot be stopped while connections areopen.

4.9.2.Coding Applications to use DRCP

To use DRCP, application connection establishment must request a DRCP pooledserver and should specify a user-chosen connection class name. A ‘purity’ ofthe connection session state can optionally be specified. See the OracleDatabase documentation onbenefiting from scalability for more informationon purity and connection classes.

The best practice is to use DRCP in conjunction with a local driverconnection pool created withoracledb.create_pool() ororacledb.create_pool_async(). Thepython-oracledb connection pool size does not need to match the DRCP pool size.The limit on overall execution parallelism is determined by the DRCP poolsize. Note that when using DRCP with a python-oracledb local connection pool inThick mode, the local connection poolmin value is ignored and the poolwill be created with zero connections.

Seedrcp_pool.pyfor a runnable example of DRCP.

Requesting Pooled Servers be Used

To enable connections to use DRCP pooled servers, you can:

• Use a specific connection string inoracledb.create_pool() ororacledb.connect() to request a pooled server. For example with theEasy Connect syntax:

  pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP")

• Alternatively, add(SERVER=POOLED) to theConnect Descriptor such as used in an Oracle Network configuration filetnsnames.ora:

  customerpool=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUSTOMER)(SERVER=POOLED)))

• Another way to use a DRCP pooled server is to set theserver_typeparameter during standalone connection creation or python-oracledbconnection pool creation. For example:

  pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb",min=2,max=5,increment=1,server_type="pooled",cclass="MYAPP")

DRCP Connection Classes

The best practice is to specify acclass class name when creating apython-oracledb connection pool. This user-chosen name provides somepartitioning of DRCP session memory so reuse is limited to similarapplications. It provides maximum pool sharing if multiple applicationprocesses are started and use the same class name. A class name also allowsbetter DRCP usage tracking in the database. In the database monitoring views,the class name shown will be the value specified in the application prefixedwith the user name.

Ifcclass was not specified during pool creation, then the python-oracledbThin mode generates a unique connection class with the prefix “DPY” while theThick mode generates a unique connection class with the prefix “OCI”.

To create a connection pool requesting DRCP pooled servers be used, andspecifying a class name, you can call:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP")

Ifcclass is not set, then the pooled server sessions will not be reusedoptimally, and theDRCP statistic views may recordlarge values for NUM_MISSES.

DRCP Connection Purity

DRCP allows the connection session memory to be reused or cleaned each time aconnection is acquired from the pool. The pool or connection creationpurity parameter can be one ofPURITY_NEW,PURITY_SELF, orPURITY_DEFAULT. The valuePURITY_SELF allows reuse of both the pooledserver process and session memory, giving maximum benefit from DRCP. Bydefault, python-oracledb pooled connections usePURITY_SELF and standaloneconnections usePURITY_NEW.

To limit session sharing, you can explicitly require that new session memory beallocated each timeacquire() is called. Do this whencreating a driver connection pool by specifying thepurity asPURITY_NEW:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP",purity=oracledb.PURITY_NEW)

The overheads can impact ultimate scalability.

DRCP Pool Names

From Oracle Database 23ai, multiple DRCP pools can be created by setting a poolname at DRCP pool creation time. Applications can then specifiy which DRCP poolto use by passing thepool_name parameter during connection, or connectionpool, creation, for example:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP",pool_name="MYPOOL")

When specifying a pool name, you should still set a connection class name toallow efficient use of the pool’s resources.

Acquiring a DRCP Connection

Once DRCP has been enabled and the driver connection pool has been created withthe appropriate connection string, then your application can get a connectionthat uses DRCP by calling:

connection=pool.acquire()

Connection class names can also be passed toacquire()if you want to use a connection with a different class:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP")connection=mypool.acquire(cclass="OTHERAPP")

If a pooled server of a requested class is not available, a server with newsession state is used. If the DRCP pool cannot grow, a server with a differentclass may be used and its session state cleared.

Closing Connections when using DRCP

Similar to using a python-oracledb connection pool, Python scripts wherepython-oracledb connections do not go out of scope quickly (which releasesthem), or do not currently useConnection.close() orConnectionPool.release() should be examined to see if the connectionscan be closed earlier. This allows maximum reuse of DRCP pooled servers byother users:

pool=oracledb.create_pool(user="hr",password=userpwd,dsn="dbhost.example.com/orclpdb:pooled",min=2,max=5,increment=1,cclass="MYAPP")# Do some database operationsconnection=mypool.acquire()...connection.close()# <- Add this to release the DRCP pooled server# Do lots of non-database work...# Do some more database operationsconnection=mypool.acquire()# <- And get a new pooled server only when needed...connection.close()

4.9.3.Setting DRCP Parameters in Connection Strings

Setting the DRCP connection class, purity, and pool name as function parametersin the application is preferred, but sometimes it is not possible to modify anexisting code base. For these applications, you can specify the values alongwith the pooled server option in the connection string.

You can specify the class and purity options in connection strings when usingOracle Database 21c, or later. You can specify the pool name when using OracleDatabase 23ai, or later.

For example with theEasy Connect syntax:

dbhost.example.com/orclpdb:pooled?pool_connection_class=MYAPP&pool_purity=self&pool_name=MYPOOL

Or by using aTNS Alias in atnsnames.ora file:

customerpool=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb)(SERVER=POOLED)(POOL_CONNECTION_CLASS=MYAPP)(POOL_PURITY=SELF)(POOL_NAME=MYPOOL)))

Explicitly specifying the purity asSELF in a connection string may causesome unusable connections in a python-oracledb Thick mode connection pool tonot be terminated, potentially eventually rendering all connections in the poolto be unusable. If you cannot programmatically set the class name and purity,or cannot use python-oracledb Thin mode, then avoid explicitly setting thepurity as a connection string parameter when using a local python-oracledbThick mode connection pool.

4.9.4.Monitoring DRCP

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

• DBA_CPOOL_INFO

• V$PROCESS

• V$SESSION

• V$CPOOL_STATS

• V$CPOOL_CC_STATS

• V$CPOOL_CONN_INFO

DBA_CPOOL_INFO View

DBA_CPOOL_INFO displays configuration information about the DRCP pool. Thecolumns are equivalent to thedbms_connection_pool.configure_pool()settings described in the table of DRCP configuration options, with theaddition of a STATUS column. The status isACTIVE if the pool has beenstarted andINACTIVE otherwise. Note that the pool name column is calledCONNECTION_POOL. This example checks whether the pool has been started andfinds the maximum number of pooled servers:

SQL>SELECTconnection_pool,status,maxsizeFROMdba_cpool_info;CONNECTION_POOLSTATUSMAXSIZE------------------------------------------------SYS_DEFAULT_CONNECTION_POOLACTIVE40

V$PROCESS and V$SESSION Views

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

V$CPOOL_STATS View

The V$CPOOL_STATS view displays information about the DRCP statistics foran instance. The V$CPOOL_STATS view can be used to assess how efficient thepool settings are. This example query shows an application using the pooleffectively. The low number of misses indicates that servers and sessions werereused. The wait count shows just over 1% of requests had to wait for a pooledserver to become available:

NUM_REQUESTSNUM_HITSNUM_MISSESNUM_WAITS------------------------------------------1003199990401055

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

V$CPOOL_CC_STATS View

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

SQL> select cclass_name, num_requests, num_hits, num_misses     from v$cpool_cc_stats;CCLASS_NAME                      NUM_REQUESTS   NUM_HITS NUM_MISSES-------------------------------- ------------ ---------- ----------HR.MYCLASS                             100031      99993         38

The class name columns shows the database user name appended with theconnection class name.

V$CPOOL_CONN_INFO View

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

You can monitor the view V$CPOOL_CONN_INFO to, for example, identifymisconfigured machines that do not have the connection class set correctly.This view maps the machine name to the class name. In python-oracledb Thickmode, the class name will be default to one like shown below:

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 oncjlinux and make themsetcclass.

When connecting to Oracle Autonomous Database on Shared Infrastructure (ADB-S),the V$CPOOL_CONN_INFO view can be used to track the number of connectionhits and misses to show the pool efficiency.

4.12.1.Using an Oracle Wallet for External Authentication

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

In this example the wallet is created for themyuser schema in the directory/home/oracle/wallet_dir. Themkstore command is available from a fullOracle client or Oracle Database installation. If you have been given wallet byyour DBA, skip to step 3.

1. First create a new wallet as theoracle 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 currentlyhardcoded 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"-createCredentialdbhost.example.com/orclpdbmyusermyuserpw

   Method 2 - Using a connect name identifier:

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

   The alias keymynetalias immediately following the-createCredential option will be the connect name to be used in Pythonscripts. If your application connects with multiple different databaseusers, you could create a wallet entry with different connect names foreach.

   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 intnsnames.ora for theconnect name as follows:

   mynetalias=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orclpdb)))

   The file uses the description for your existing database and sets theconnect name alias tomynetalias, which is the identifier used whenadding the wallet entry.

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

   WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet_dir)))SQLNET.WALLET_OVERRIDE=TRUE

   Examine the Oracle documentation for full settings and values.

5. Ensure the configuration files are in a default location or TNS_ADMIN isset to the directory containing them. SeeOptional Oracle Net Configuration Files.

With an Oracle wallet configured, and readable by you, your scriptscan connect to Oracle Database with:

• Standalone connections by setting theexternalauth parameter toTrueinoracledb.connect():

  connection=oracledb.connect(externalauth=True,dsn="mynetalias")

• Or pooled connections by setting theexternalauth parameter toTrueinoracledb.create_pool(). Additionally in python-oracledb Thickmode, you must set thehomogeneous parameter toFalse as shown belowsince heterogeneous pools can only be used with external authentication:

  pool=oracledb.create_pool(externalauth=True,homogeneous=False,dsn="mynetalias")pool.acquire()

Thedsn used inoracledb.connect() andoracledb.create_pool() must match the one used in the wallet.

After connecting, the query:

SELECTSYS_CONTEXT('USERENV','SESSION_USER')FROMDUAL;