ORA-12514 and ORA-12154: SID vs Service Name vs TNS, Explained

Posted by Kyle Hankinson August 11, 2026

Someone hands you a host, a port, and "the database name", you type them into a client, and Oracle answers with this:

ORA-12514: Cannot connect to database. Service SALESDB is not
registered with the listener at host 127.0.0.1 port 1521.

That is the current wording; releases before 23ai phrase it as ORA-12514: TNS:listener does not currently know of service requested in connect descriptor, which is the string most search results still show. Either way, the error never says what is usually wrong: you gave the right name of the wrong kind, or the right kind with a stale name. Oracle has three different ways to identify a database, and every ORA-125xx error is really telling you which layer of the connection gave up.

The three identifiers

SID (system identifier) names an instance, the set of Oracle processes and memory running on the server. It is the oldest scheme, and old JDBC strings use it with a colon: host:1521:ORCL.

Service name is what an instance registers with the listener. One instance can register several services, and since Oracle 12c each pluggable database (PDB) registers its own. EZConnect uses a slash: host:1521/FREEPDB1.

TNS alias is a client-side nickname (like PRODDB) that your local tnsnames.ora file expands into a full host/port/service descriptor. The server is not involved in resolving it at all. Oracle's Net Services guide covers how registration and resolution fit together.

The colon-versus-slash distinction matters more than it looks. host:1521:FREEPDB1 asks the listener for a SID named FREEPDB1; host:1521/FREEPDB1 asks for a service. Same characters, different question, different error when it fails.

One error per layer

A connection attempt passes through your client config, the network, the listener, and finally database authentication. Each stage has its own failure code, so the error you get locates the problem for you:

Error	Layer that failed	What it actually means
ORA-12154	your machine	The TNS alias could not be resolved. The server was never contacted.
ORA-12541	network	Nothing is listening at that host and port. Wrong port, server down, or a firewall.
ORA-12514	listener	The listener is up but no service by that name is registered.
ORA-12505	listener	The listener is up but no SID by that name is registered.
ORA-01017	database	You reached the database. Username or password is wrong.

I reproduced all five against Oracle Database Free 23ai running in Docker (gvenzl/oracle-free:23-slim), and the modern messages are refreshingly explicit. A wrong SID:

ORA-12505: Cannot connect to database. SID FREEPDB1 is not registered
with the listener at host 127.0.0.1 port 1521.

Note what happened there: FREEPDB1 is a perfectly valid service on that server, but I asked for it as a SID and the listener refused. That single test is the whole SID-versus-service confusion in miniature. A wrong port:

ORA-12541: Cannot connect. No listener at host 127.0.0.1 port 1599.

An alias missing from tnsnames.ora:

ORA-12154: Cannot connect to database. Cannot find alias PRODDB in
/opt/oracle/product/26ai/dbhomeFree/network/admin/tnsnames.ora.

ORA-12154 deserves special emphasis because people burn hours restarting servers over it: the message names a file on your own machine. The network was never touched. Fix the alias, point TNS_ADMIN at the right directory, or skip TNS entirely and use host:port/service directly.

And with everything right except the password:

ORA-01017: invalid credential or not authorized; logon denied

One footnote on ORA-01017: passwords have been case-sensitive since 11g, so a password that worked on an ancient system in uppercase may fail verbatim on a newer one.

Finding the service name you actually need

When ORA-12514 strikes, stop guessing and ask the server what it has. On the database host:

$ lsnrctl status
...
Services Summary...
Service "FREE" has 1 instance(s).
Service "FREEXDB" has 1 instance(s).
Service "freepdb1" has 1 instance(s).

Or, from any session that can connect (a DBA, or you via a different tool):

SELECT name FROM v$services;

On my container that returns exactly one row, freepdb1, which is the service application connections should use. Service names are case-insensitive when you connect, so FREEPDB1 works fine.

Why your old SID stopped working: multitenant

Since 12c, Oracle's multitenant architecture splits a server into a container database (CDB) and pluggable databases (PDBs), and from 21c on, multitenant is the only option. Your tables live in a PDB, and a PDB is reachable only by service name. It has no SID.

This is the story behind most "it worked before the upgrade" tickets. The old host:1521:ORCL string named the instance; after migration to multitenant, that instance is the CDB, and your schema now lives in a PDB like ORCLPDB1. Depending on the driver, the SID string either fails outright or, worse, connects you to the CDB root where none of your tables exist, producing mysterious ORA-00942 errors on tables you can see in another tool. On the Docker image the split is visible immediately: service FREE is the CDB, FREEPDB1 is the PDB where the app user's schema lives.

The rule of thumb for anything modern: use the service name, with a slash. Reserve SID syntax for legacy servers that genuinely predate services, which in practice means almost nothing still in production.

Plugging the right value into a GUI client

Connection editors mirror the same distinction, so this decoder maps directly onto the form fields. SQLPro Studio's Oracle connection editor, for example, takes a host, a port, and a name you mark as either a service name or a SID; choosing the wrong kind produces exactly the ORA-12514 or ORA-12505 you would get on the command line, so the table above tells you which toggle to flip. For a local playground, docker run -d -p 1521:1521 -e ORACLE_PASSWORD=test gvenzl/oracle-free:23-slim gives you a server whose answers are always the same: port 1521, service name FREEPDB1.

A last diagnostic habit worth keeping: read the error as a progress report. ORA-12154 means you never left the laptop. ORA-12541 means you found the machine but not the listener. ORA-12514 and ORA-12505 mean the listener heard you and vetoed the name. ORA-01017 means the whole network path is fine and only the credentials are wrong, so stop editing tnsnames.ora. Each code eliminates every layer before it, and working through them in order beats changing three settings at once.

Once you are connected, Oracle has a few more surprises waiting for arrivals from other databases; the first one most people hit is pagination, covered in Oracle pagination: ROWNUM vs FETCH FIRST. Oracle also maintains reference pages for each code at docs.oracle.com/error-help, and current releases print that link under the error message itself.

Tags: Oracle

Oracle Pagination: ROWNUM vs FETCH FIRST (and the ROWNUM > 1 Trap)

Posted by Kyle Hankinson July 24, 2026

If you learned SQL on MySQL or PostgreSQL, your first attempt to limit rows in Oracle probably looked like this:

SELECT id, total FROM orders LIMIT 10;

Oracle rejects it. On a current release (I tested against Oracle Database Free 23ai in Docker) the parser says:

ORA-03047: number '10' is not syntactically valid following
'...id, total FROM orders LIMIT '

Older versions raise the vaguer ORA-00933: SQL command not properly ended. Either way, there is no LIMIT keyword in Oracle. There are two replacements: the standard row-limiting clause added in Oracle 12.1, and the much older ROWNUM pseudocolumn, which carries two traps that produce wrong results without any error message. Here is the modern syntax first, then what ROWNUM actually does, because you will still meet it in old code and old answers.

The modern way: FETCH FIRST (Oracle 12.1 and later)

Oracle 12.1 (2013) added the SQL-standard row limiting clause:

SELECT id, customer, total
FROM   orders
ORDER  BY total DESC
FETCH  FIRST 10 ROWS ONLY;

Pagination adds an OFFSET. Page 3 with 10 rows per page:

SELECT id, customer, total
FROM   orders
ORDER  BY total DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

I ran both against a 100-row test table and they behave exactly like LIMIT/OFFSET elsewhere. Two details worth knowing:

The ORDER BY is technically optional, but without it Oracle hands back an arbitrary 10 rows, so pages can overlap or skip rows between requests. Always order by something unique (add the primary key as a tie-breaker).
FETCH FIRST 3 ROWS WITH TIES keeps rows that tie with the last one, and FETCH FIRST 10 PERCENT ROWS ONLY works too.

If you are on 12.1 or later, use this and stop reading here if you like. The rest of the article is about the pre-12c pattern and the two ways it silently goes wrong.

What ROWNUM actually is

ROWNUM is a pseudocolumn that numbers rows in the order Oracle fetches them from the filtered result set. The first row that passes the WHERE clause gets ROWNUM 1, the second gets 2, and so on. Two consequences follow directly from that definition, and each one is a classic bug.

Trap 1: WHERE ROWNUM > 1 returns zero rows, always

SELECT id, customer, total FROM orders WHERE ROWNUM > 1;

Against my 100-row table:

no rows selected

No error, no warning, just an empty result. The mechanics: the first candidate row is offered ROWNUM 1. The predicate 1 > 1 is false, so the row is rejected. Because it was rejected, the counter never advances, and the next candidate row is offered ROWNUM 1 again. Every row fails the same test forever. Oracle's own documentation states that conditions testing for ROWNUM values greater than a positive integer are always false.

The same logic kills WHERE ROWNUM = 5, which also returned no rows selected in my test. Only ROWNUM = 1 and ranges anchored at 1 (ROWNUM <= n, ROWNUM < n) can ever be true. If you need "rows 21 to 30", you cannot do it with a bare ROWNUM predicate; you need one of the patterns below.

Trap 2: ROWNUM is assigned before ORDER BY

This one is nastier because it returns plausible-looking data. The intent here is "the ten biggest orders":

-- WRONG: filters first, sorts the leftovers
SELECT id, customer, total
FROM   orders
WHERE  ROWNUM <= 10
ORDER  BY total DESC;

Oracle applies ROWNUM <= 10 while fetching, which grabs the first ten rows in whatever order the table returns them, and only then sorts those ten. Against my test table (100 orders with random totals), the wrong query returned ids 1 through 10, nicely sorted, including rows with totals of 60.61 and 55.73. The correct version pushes the ORDER BY into a subquery so sorting happens before ROWNUM is assigned:

-- RIGHT: sort first, then take the top of the sorted set
SELECT id, customer, total
FROM  (SELECT id, customer, total FROM orders ORDER BY total DESC)
WHERE ROWNUM <= 10;

That returned a genuinely different set: the true top ten, with the lowest total at 458.01. Seven of the ten rows differed between the two queries. Nothing about the wrong query's output hints that it is wrong, which is why this bug survives code review.

Offset pagination before 12c

On 11g and earlier, "skip 20, take 10" needs a double-nested query, because ROWNUM must be materialized in an inner block before you can filter on its higher values:

SELECT id, customer, total
FROM (
  SELECT t.*, ROWNUM AS rn
  FROM  (SELECT id, customer, total FROM orders ORDER BY total DESC) t
  WHERE ROWNUM <= 30
)
WHERE rn > 20;

The analytic alternative reads more clearly and gives the same rows (I verified both return identical output for the same page):

SELECT id, customer, total
FROM (
  SELECT o.*, ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
  FROM   orders o
)
WHERE rn BETWEEN 21 AND 30
ORDER BY rn;

If ROW_NUMBER is new to you, our ROW_NUMBER vs RANK vs DENSE_RANK post covers how the numbering functions differ.

Quick reference

Oracle version	Top-N query	Offset pagination
12.1 and later	`ORDER BY ... FETCH FIRST n ROWS ONLY`	`ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY`
11g and earlier	ordered subquery + `WHERE ROWNUM <= n`	double-nested ROWNUM or `ROW_NUMBER() OVER (...)`
Any version, deep pages	keyset (seek) pagination	keyset pagination

That last row deserves a sentence. OFFSET-style pagination reads and throws away every skipped row, so page 5,000 is expensive. Keyset pagination filters on the last value seen instead:

SELECT id, customer, total
FROM   orders
WHERE  total < :last_total
   OR (total = :last_total AND id < :last_id)
ORDER  BY total DESC, id DESC
FETCH  FIRST 10 ROWS ONLY;

I verified this picks up exactly where the previous page ended. It cannot jump to an arbitrary page number, but for infinite-scroll style access it scales far better. For a cross-engine view of LIMIT, TOP, and FETCH FIRST, see how to limit query results and paginate in SQL.

A convenient way to convince yourself of the ORDER BY trap is to run the wrong and right variants in two query tabs of SQLPro Studio against an Oracle connection and compare the result grids side by side; the diverging rows are hard to miss. If you want a sandbox to try it on, one command gives you a disposable Oracle on an ARM or Intel Mac: docker run -d -p 1521:1521 -e ORACLE_PASSWORD=test gvenzl/oracle-free:23-slim, then connect to service FREEPDB1.

The habit that keeps you safe: never filter on ROWNUM in the same query block as an ORDER BY, and never compare ROWNUM against anything other than a range starting at 1. On any Oracle from the last decade, skip the ceremony and write FETCH FIRST.