← Connection strings

Oracle connection strings

Oracle names a database by its service, and most connection errors come from getting that one word wrong. The short form is Easy Connect, host:port/service_name, which python-oracledb, node-oracledb, JDBC and SQL*Plus all accept; the long form is a connect descriptor, the same thing tnsnames.ora holds. Below is each form for the same database.

Build yours (Oracle)

The parts of a Oracle connection string

The shape

host:1521/service_name
host:1521
TCPS (TLS) listeners are often on 2484; prefix tcps:// to use one.
/service_name
The service, e.g. FREEPDB1 on 23ai Free, XEPDB1 on 21c XE — not the SID.
user / password
Given separately to the driver, not inside Easy Connect.

One Oracle database, every form

User app_user, password s3cret-Pa55, service name ORCLPDB1 on db.example.com, tls, verify the certificate.

Easy Connect

python-oracledb, node-oracledb, SQL*Plus, SQLcl (user and password given separately) · connected with on a real server

tcps://db.example.com:1521/ORCLPDB1

python-oracledb

Python (thin mode, no Oracle Client needed) · connected with on a real server

oracledb.connect(user="app_user", password="s3cret-Pa55", dsn="tcps://db.example.com:1521/ORCLPDB1")

JDBC (thin)

Java, DBeaver, SQL Developer, Spring

jdbc:oracle:thin:@tcps://db.example.com:1521/ORCLPDB1

User and password go in the driver's properties, not the URL.

SQLAlchemy

Python with SQLAlchemy 2 and python-oracledb

oracle+oracledb://app_user:s3cret-Pa55@db.example.com:1521/?service_name=ORCLPDB1&protocol=tcps

.NET (ODP.NET)

C#, EF Core

User Id=app_user;Password=s3cret-Pa55;Data Source=tcps://db.example.com:1521/ORCLPDB1;

Connect descriptor (tnsnames.ora)

Anything that reads tnsnames.ora, or takes a descriptor as the DSN

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=db.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)))

SQL*Plus command

Shell (SQLcl's sql takes the same argument) · connected with on a real server

sqlplus app_user@tcps://db.example.com:1521/ORCLPDB1

Asks for the password. A password with special characters works inline in double quotes, user/"pa@ss word"@//host:1521/service, with single quotes around it all in the shell (it then lands in your shell history).

A password with special characters

The same database with the password p@ss/w#rd 1. URLs encode it, key=value forms quote it, and each form does it differently:

Easy Connect

tcps://db.example.com:1521/ORCLPDB1

python-oracledb

oracledb.connect(user="app_user", password="p@ss/w#rd 1", dsn="tcps://db.example.com:1521/ORCLPDB1")

JDBC (thin)

jdbc:oracle:thin:@tcps://db.example.com:1521/ORCLPDB1

SQLAlchemy

oracle+oracledb://app_user:p%40ss%2Fw%23rd%201@db.example.com:1521/?service_name=ORCLPDB1&protocol=tcps

CharacterIn a URLWhy it breaks
@%40Ends the user name and password: everything after the first unencoded @ is read as the host.
:%3ASeparates user from password, and host from port.
/%2FStarts the database name.
?%3FStarts the options.
#%23Starts a fragment: the rest of the string is thrown away.
%%25Starts an escape itself, so a literal % must be escaped too.
&%26Separates options.
+%2BSome parsers (PyMongo among them) read + as a space.
space%20Ends the string on a command line, and is trimmed by some parsers.

TLS: what each setting looks like

Shown in the JDBC (thin) form; the builder writes the matching setting for every other one.

No TLS

jdbc:oracle:thin:@//db.example.com:1521/ORCLPDB1

TLS, verify the certificate

jdbc:oracle:thin:@tcps://db.example.com:1521/ORCLPDB1

What catches people out on Oracle

Service name, not SID

host:1521/ORCLPDB1 is a service name. The old JDBC form host:1521:ORCL is a SID, which names an instance, not a pluggable database — so in a CDB it reaches the container, not your PDB. ORA-12514 means the listener knows no service by that name.

Your tables are in the PDB

Free and XE come with a container (FREE, XE) and a pluggable database (FREEPDB1, XEPDB1). Connect to the PDB service; users created in the container are common users with a C## prefix.

No Instant Client needed any more

python-oracledb in its default thin mode and node-oracledb 6 thin talk to the database directly. Only thick mode (some advanced features) needs Oracle Client libraries installed.

Names are upper case unless quoted

create user app_user makes APP_USER. Quoted names keep their case and then have to be quoted forever after — avoid them.

Special characters in passwords

A password with @, / or a space has to be in double quotes on the SQL*Plus command line and in CREATE USER: user/"pa@ss"@//host:1521/service. Oracle passwords cannot contain a double quote at all.

Connected. Now what?

Paste the same string into DBShifts to browse Oracle with sensitive columns masked and every read logged, or to migrate it to another database with each table counted and checksummed afterwards.

Questions

What is the Oracle connection string format?

Easy Connect: host:port/service_name, e.g. db.example.com:1521/FREEPDB1, with user and password passed separately. JDBC thin: jdbc:oracle:thin:@//host:1521/service_name. Long form: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=…)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=…))).

What is the difference between a service name and a SID?

A SID names one database instance; a service name names a database service, which can be offered by several instances and is how you reach a pluggable database. Use the service name: host:1521/service. The colon form host:1521:SID is the old SID syntax.

What does ORA-12514 mean?

The listener is up but doesn't know the service name you asked for. Check the name with lsnrctl status on the server, or select value from v$parameter where name = 'service_names'.

What is the default Oracle port?

1521 for TCP. TCPS (TLS) listeners are commonly configured on 2484.