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
| Character | In a URL | Why it breaks |
|---|---|---|
| @ | %40 | Ends the user name and password: everything after the first unencoded @ is read as the host. |
| : | %3A | Separates user from password, and host from port. |
| / | %2F | Starts the database name. |
| ? | %3F | Starts the options. |
| # | %23 | Starts a fragment: the rest of the string is thrown away. |
| % | %25 | Starts an escape itself, so a literal % must be escaped too. |
| & | %26 | Separates options. |
| + | %2B | Some parsers (PyMongo among them) read + as a space. |
| space | %20 | Ends 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.
More on Oracle
Other databases
PostgreSQL connection strings
postgresql://user:password@host:5432/database?sslmode=verify-full
MySQL connection strings
mysql://user:password@host:3306/database
MariaDB connection strings
jdbc:mariadb://host:3306/database?user=…&password=…&sslMode=verify-full
SQL Server connection strings
Server=tcp:host,1433;Database=…;User Id=…;Password=…;Encrypt=True;
MongoDB connection strings
mongodb://user:password@host:27017/database?authSource=admin&tls=true
SQLite connection strings
sqlite:////var/data/app.db