6.6 Viewing Connection Strings Using the connstr Utility

You can run the Oracle Database Connection String command-line utility (connstr) to display Oracle Database connect strings for all the available network service names.

You can use these strings in a client application or tool (such as SQL*Plus, Python, or JDBC Thin) to quickly connect to Oracle Database, or write these service name entries to the tnsnames.ora file for use with the local naming method.

The connstr script is included in a default installation. The utility displays connect strings for a single Oracle software installation, based on the available system configuration.

Note:

This utility is suitable for single-instance or Oracle Database Free installations but not for more complex configurations, such as Oracle Real Application Clusters (Oracle RAC) or Oracle Data Guard. It relies on the Listener Control utility (lsnrctl), and thus you must run this utility on a computer hosting your Oracle Database and listener. However, you can use the displayed connect strings on any supported client system.

1. Enter the following command at the command line:

   connstr

   This utility runs in an interactive mode with default options, without the need to specify any arguments.

   An output appears with a list of service names and their corresponding connect strings in the Easy Connect format. The output displays sample strings to be used in SQL*Plus, Python, or JDBC Thin clients. See Example 6-1.
2. (Optional) If you want to run this utility with advanced configuration options, then use the -h (or --help) option to access a list of additional arguments that you can enter at the command line:

   connstr -h

   Here are some commonly used arguments that you may use with connstr:

   • This utility uses the default listener named LISTENER. If you are using a different listener or if multiple listeners are active, then use -L (or --listener) to specify the listener name:

     connstr -L listener_name

     When run, an interactive dialog appears with various choices depending on the listener configuration.

   • To filter a list of available listener endpoints, use -e (or --endpoints):

     connstr -e

     For example:

     connstr -e
     PROTOCOL  PORT   HOST
     tcp       1521   sales-server
     tcp       1523   hr-server
     tcps      1522   mktg-server

     To filter a list of available listener services, use -s (or --services):

     connstr -s

     For example:

     connstr -s
     sales.us.example.com
     hr.us.example.com
     mktg.us.example.com

   • By default, this utility displays strings in the Easy Connect format. If you want to connect using a JDBC Thin application, then use -j (or --jdbc) along with the service name to view a connect string in the JDBC Thin format:

     connstr -j service_name

     For example:

     connstr -j sales.us.example.com
     jdbc:oracle:thin:@sales-server:1521/sales.us.example.com

     To display a connect string in the connect descriptor format, use -d (or --descriptor):

     connstr -d service_name

     For example:

     connstr -d sales.us.example.com
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
     (CONNECT_DATA=(SERVICE_NAME=sales.us.example.com)))

     To display a connect string in the default (Easy Connect) format, use -z (or --ezconnect):

     connstr -z service_name

     For example:

     connstr -z sales.us.example.com
     username@sales-server:1521/sales.us.example.com

3. The output prompts you to write connect strings to the tnsnames.ora file. If you want to create this file with the available service names and their connect descriptors, then enter Y.

   The tnsnames.ora file is written in the directory specified by the TNS_ADMIN environment variable. If the TNS_ADMIN environment variable is not set, then the file is written either in the ORACLE_BASE_HOME/network/admin directory or in the ORACLE_HOME/network/admin directory.

   If the tnsnames.ora file already exists, then you are prompted to either append these entries to the existing file or overwrite the file. Enter Y to append or O to overwrite.

Example 6-1 Sample connstr Utility Output in the Easy Connect Format

Using Listener: LISTENER with Oracle Home: /app/oracle/product/23ai/dbhome_1 

Service Name: sales.us.example.com 
Connection String: sales-server:1521/sales.us.example.com 

Connection strings can be used to connect to the specified service name. 

For SQL*Plus you can use: 
SQL> connect username@sales-server:1522/sales.us.example.com

For Python you can use: 
connection = cx_Oracle.connect(user="username", password="password", 
dsn="sales-server:1521/sales.us.example.com") 

For JDBC Thin you can use: 
OracleDataSource ods = new OracleDataSource(); 
ods.setURL("jdbc:oracle:thin:@sales-server:1521/sales.us.example.com"); 
ods.setUser("username"); ods.setPassword("password"); 
Connection conn = ods.getConnection(); 

Write connect strings to tnsnames.ora (Y/N)? (Default: N): y