ALTER USER app_user IDENTIFIED BY "Summer_Passw0rd2";

The new password goes into the secrets store, and the app servers pick it up as they restart. Twenty minutes later the application is down, and the error is:

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

followed by:

ORA-28000: The account is locked

ORA-01017 on its own just means a wrong username or password. After a password change it means something more specific: some clients still have the old one. Their retries use up the account’s failed-login limit, the account locks, and from then on it refuses every login, including from the servers that already have the new password. The change was correct. The way it rolled out caused the outage.

Oracle has a feature for exactly this: gradual password rollover, in 19c from Release Update 19.12 and in 21c and later. For a window you choose, the old and the new password both work. This post shows the outage, the feature, the query that tells you which clients are still on the old password, and the two traps that come with it. All of it is reproduced in a lab.

Why one password change takes everything down

Three things combine.

Existing connections don’t notice. A password is checked only at login. Every session that was open before the change keeps working. So the change looks fine at first. The failures start later, when a connection pool recycles, a server restarts, or traffic grows and the pool opens new connections.

Not every client changes at the same moment. App servers restart one at a time. A batch job reads its password from a different file. A reporting tool has the password saved in a connection profile nobody remembers. Some of them are on the old password for minutes, some for days.

Failed logins lock the account, not the client. The profile’s FAILED_LOGIN_ATTEMPTS (10 in a stock DEFAULT profile) counts failures for the account. Once a few clients have retried with the old password, the account is LOCKED(TIMED) for PASSWORD_LOCK_TIME (1 day by default), and the correct password gets ORA-28000 too.

So the incident looks like this: a password change, a quiet period, then ORA-01017 from a few servers, then ORA-28000 from all of them.

Prove it

The lab runs Oracle Database Free in a container. It creates APP_USER on a profile with FAILED_LOGIN_ATTEMPTS 5, and audits its logins. Then it changes the password while "app servers" still log in with the old one:

password change, no rollover               result
old password, attempts 1-5                  ORA-01017: invalid credential or not authorized
old password, attempt 6                     ORA-28000: The account is locked
new password                                ORA-28000: The account is locked
connection opened before the change         still alive, still querying

Then the same change with a one-day rollover window:

password change, PASSWORD_ROLLOVER_TIME 1   result
account status                              OPEN & IN ROLLOVER
old password, logins 1-7                    connected (7 logins, limit is 5 failures)
new password                                connected

Don’t take my word for it — run it. The password-rollover lab asserts every row: ORA-01017 five times, then ORA-28000 for the old and the new password without rollover; both passwords working with it; the audit trail telling them apart; a typo still locking the account during rollover; a second change dropping the middle password; and ending the window early. If any of that doesn’t reproduce, the run fails. Proven on every CI push.

Gradual password rollover

Rollover is a profile limit, not an option of ALTER USER. Set it on the profile the application account uses, then change the password:

ALTER PROFILE app_prof LIMIT password_rollover_time 1;   -- days: 1/24 = one hour

ALTER USER app_user IDENTIFIED BY "Summer_Passw0rd2";

From that moment until the window ends, both passwords log in, and DBA_USERS shows it:

SELECT username, account_status FROM dba_users WHERE username = 'APP_USER';

USERNAME   ACCOUNT_STATUS
APP_USER   OPEN & IN ROLLOVER

The window is in days, from one hour (1/24) to 60 days; anything outside that is refused with ORA-02377 (the lab checks 30 minutes and 61 days). 0, the DEFAULT profile's value, turns it off. The window starts at the password change, so a profile changed after the password doesn't help that change.

Pick a window longer than your slowest client takes to pick up the new password, including the weekly batch job and the server that only restarts at patch time. A day is short. A week is common.

A password rotation with rollover. The window keeps the old password working while clients move; the audit trail tells you when they all have; then you end the window instead of waiting for it.

Who is still on the old password?

This is the question the rollover window gives you time to answer, and the database can answer it. With logins audited, every unified audit record for a successful login says which password it used, in AUTHENTICATION_TYPE:

(TYPE=(DATABASE));(CLIENT ADDRESS=((PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=56796)));
(LOGON_INFO=((VERIFIER=12C-OLD)(CLIENT_CAPABILITIES=O5L_NP,O7L_MR,O8L_LI)));

12C-OLD is the old password, 12C-NEW the new one. (That's what the lab's 26ai database records; check that your release writes LOGON_INFO before you rely on it. If it doesn't, the same query without the verifier still lists every host and program that logged in since the change, which is your list of clients to check.) Count them since the change:

SELECT regexp_substr(authentication_type, 'VERIFIER=([^)]*)', 1, 1, NULL, 1) AS verifier,
       userhost, client_program_name, COUNT(*) AS logons
FROM   unified_audit_trail
WHERE  dbusername = 'APP_USER'
AND    action_name = 'LOGON'
AND    return_code = 0
AND    event_timestamp >= (SELECT CAST(password_change_date AS TIMESTAMP)
                           FROM dba_users WHERE username = 'APP_USER')
GROUP  BY regexp_substr(authentication_type, 'VERIFIER=([^)]*)', 1, 1, NULL, 1),
          userhost, client_program_name
ORDER  BY verifier, userhost;

In the lab (all clients on one host, so grouped by verifier alone), after seven logins with the old password and one with the new:

VERIFIER=12C-NEW LOGONS=1
VERIFIER=12C-OLD LOGONS=7

Every -OLD row is a client you still have to update, with its host and program next to it. This needs logins to be audited. The lab creates a policy for the account:

CREATE AUDIT POLICY app_logons ACTIONS LOGON;
AUDIT POLICY app_logons BY app_user;

In the lab’s database nothing audited logins until that policy existed; check AUDIT_UNIFIED_ENABLED_POLICIES on yours. Successful logins can be a lot of rows for a busy pool, so scope the policy to the accounts you're rotating, and enable it before the change. The same trail answers the outage question, too: without rollover, the failed logins showed up as ORA-01017 x5 and ORA-28000 x2, with the client host, and that's how you find the server that locked the account. See unified auditing without the noise for keeping policies like this small.

Ending the window

When the audit trail shows no more -OLD logins for long enough to cover your slowest client, end the window instead of waiting it out:

ALTER USER app_user EXPIRE PASSWORD ROLLOVER PERIOD;

In the lab the status goes back to OPEN, the old password gets ORA-01017, the new one connects, and a connection opened earlier with the old password is still alive and querying. Ending the rollover, like the change itself, only affects new logins.

That’s also the reason to end it deliberately. While the window is open, anyone with the old password can still log in. If the rotation was because the old password leaked, a long window keeps the leak open, so pick the window to match the reason for the change.

Two traps

A wrong password still counts. Rollover accepts two passwords, not any password. In the lab, five logins with a typo during the rollover window got ORA-01017, and then the account was locked: the new password and the old one both got ORA-28000. A client with a mistyped secret locks the account just as it did before. Rollover removes the outage caused by timing; it doesn’t remove the lockout.

A second change drops the middle password. If the password is changed again inside the window (a rerun of the rotation job, a second ticket, someone “fixing” a typo in the new password), the window continues, but only the original and the latest password work:

original password (Spring)        connected
first new password (Summer)       ORA-01017
latest password (Autumn)          connected
account status                    OPEN & IN ROLLOVER

Any client that already moved to the middle password is now failing, and the account status looks exactly the same as it did a minute earlier. If you need a second change, let the first rollover finish (or end it) first.

The rotation, step by step

1. Know the clients. Audit logins for the account for a while before the change. Each distinct USERHOST / CLIENT_PROGRAM_NAME is a client that needs the new password.
2. Set the window on the account’s profile: ALTER PROFILE ... LIMIT password_rollover_time 7. If the profile is shared with accounts you don't want to roll over, give this account its own profile.
3. Change the password once. ALTER USER ... IDENTIFIED BY .... Check OPEN & IN ROLLOVER.
4. Push the new password to every client, at whatever pace your deployments allow.
5. Watch the audit trail until there are no -OLD logins, and chase the ones that remain.
6. End the window: ALTER USER ... EXPIRE PASSWORD ROLLOVER PERIOD.

If the account is already locked when you start reading this: ALTER USER app_user ACCOUNT UNLOCK, find the client that's still failing (the audit trail has its host, if failed logins are audited), and fix it before it locks the account again.

What teams get wrong

• Changing the password and the clients “at the same time.” There’s no such moment. Some client is always last, and until it moves, it’s failing logins against a shared limit.
• Setting the rollover limit after the password change. The window starts at the change. Set the profile first.
• Reading OPEN & IN ROLLOVER as "the old password works." After a second change it's the original password that works, not the one most clients were just given.
• Raising FAILED_LOGIN_ATTEMPTS to UNLIMITED so rotations stop locking the account. That removes a defence against password guessing for good, to fix a problem that lasts an hour. Use rollover, and keep the limit (see the hardening checklist).
• Leaving the window open for weeks after everyone has moved. It’s two valid passwords. End it once the audit trail is clean.

Frequently asked questions

Why do I get ORA-01017 after changing a user’s password?

Because some client is still logging in with the old password. Sessions opened before the change keep working, so the failures appear later, when a connection pool opens new connections or a server restarts. Each failed login counts against the profile’s FAILED_LOGIN_ATTEMPTS, and once the limit is reached the account locks and every login, including ones with the new password, fails with ORA-28000.

What is gradual password rollover in Oracle?

A profile limit, PASSWORD_ROLLOVER_TIME, available in Oracle 19c from Release Update 19.12 and in 21c and later. When an account’s password is changed, both the old and the new password are accepted for that many days, so clients can move to the new password one at a time without login failures. The account status shows OPEN & IN ROLLOVER during the window. The window can be from one hour (1/24) to 60 days; 0 turns it off.

How do I find which clients still use the old password during a rollover?

Audit logins for the account (CREATE AUDIT POLICY … ACTIONS LOGON and AUDIT POLICY … BY the user). Each successful login’s AUTHENTICATION_TYPE in UNIFIED_AUDIT_TRAIL includes LOGON_INFO with VERIFIER=12C-OLD or VERIFIER=12C-NEW. Group the logins since the password change by verifier, USERHOST and CLIENT_PROGRAM_NAME; every OLD row is a client still to update. This was verified on Oracle 26ai; on other releases, check that LOGON_INFO is recorded.

How do I end a password rollover period early?

Run ALTER USER username EXPIRE PASSWORD ROLLOVER PERIOD. The account status returns to OPEN and only the new password works for new logins. Sessions that are already connected, including ones that logged in with the old password, are not disconnected.

Does password rollover prevent ORA-28000 account locked?

Only for the old password. During the rollover window the old and new passwords both log in, so clients that haven’t moved don’t fail. A wrong password still fails with ORA-01017 and still counts toward FAILED_LOGIN_ATTEMPTS, and in the companion lab five wrong logins during the window locked the account for both passwords.

What happens if I change the password twice during a rollover?

The rollover window continues, but only the original password (from before the first change) and the latest password are valid. The password in between stops working immediately, even though the account status still shows OPEN & IN ROLLOVER. Clients that had already moved to the middle password get ORA-01017.

ORA-01017 after a password change isn’t a mystery about the password. It’s a rollout problem: some client is always last, and without a window, the last client locks out the first. Give the account a rollover window, change the password once, watch the audit trail until nobody is on the old one, and close the window yourself. It belongs with the other account controls in the hardening checklist and with the connection errors that look like outages, like ORA-12514. Then prove it the way that ends the argument — with the password-rollover lab, where the same password change locks the account without rollover and causes zero failed logins with it.

Originally published at https://uptimearchitect.com on October 28, 2026.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Newer clients word it more plainly. The 26ai client in this post’s lab says:

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

The database is up. You can log in on the server. So someone restarts the listener, and now nothing can connect for a minute.

ORA-12514 is a precise message, and it’s good news of a kind: the listener answered. It’s running, the host and port are right, and the network path works. What it doesn’t have is a service by the name the client asked for. The database behind it may be perfectly healthy. This post covers how services get to the listener, the four common reasons one is missing, and how to tell ORA-12514 from its neighbours. It ends with a lab where restarting the listener causes a minute of ORA-12514 for every service, and one ALTER SYSTEM REGISTER closes the gap at once.

How a service gets to the listener

The listener doesn’t know what databases exist. The database tells it. A background process (LREG) in each instance registers the instance’s services with the listener: FREEPDB1, each application service, the CDB's own service. It does that at startup, whenever a service starts or stops, and periodically after that. The listener keeps that list in memory only.

A client connects by asking for a service: host:1521/FREEPDB1 in EZConnect, or SERVICE_NAME=FREEPDB1 in a tnsnames.ora descriptor. If the listener has a registered service by that name, it hands the connection to the instance. If not, it refuses with ORA-12514.

So ORA-12514 has exactly two kinds of cause: the client asked for the wrong name, or the right name isn’t registered with this listener right now. lsnrctl services (or lsnrctl status) shows what the listener has:

Services Summary...
Service "FREE" has 1 instance(s).
Service "FREEXDB" has 1 instance(s).
Service "freepdb1" has 1 instance(s).
Service "orders_app" has 1 instance(s).

If the service is in that list, look at the client. If it isn’t, look at the database.

Which error do you actually have?

Five errors get mixed up in tickets. They fail at different points in the connection, so they point to different fixes:

ORA-12154   the alias wasn't found (tnsnames.ora, LDAP)    the client never reached a listener
ORA-12541   no listener at that host and port              wrong host/port, listener down, firewall
ORA-12505   the listener doesn't know that SID             often a PDB name given as a SID
ORA-12514   the listener doesn't know that SERVICE         this post
ORA-12504   no service name given, and no default service  incomplete connect string

ORA-12505 is the usual developer version. jdbc:oracle:thin:@db01:1521:FREEPDB1 (a colon before the name) asks for a SID, which is an instance, not a PDB. jdbc:oracle:thin:@//db01:1521/FREEPDB1 (a slash) asks for the service, which is what a PDB is reached by.

Prove it

The lab runs Oracle Database Free in a container. It creates an application service, orders_app, in the pluggable database FREEPDB1 the way many shops do: with DBMS_SERVICE, started by hand. Then it breaks the connection four ways, with the database up and healthy every time:

case                                     result                                   fix
FREEPDB instead of FREEPDB1              ORA-12514; FREEPDB1 connects fine        fix the connect string
listener restarted                       ORA-12514 for every service, ~60 s       ALTER SYSTEM REGISTER
PDB closed and opened                    PDB READ WRITE, orders_app ORA-12514     start it + SAVE STATE
second listener on port 1522             "supports no services", ORA-12514        LOCAL_LISTENER

Don’t take my word for it — run it. The listener-service lab asserts every row: ORA-12514 for the wrong name and the right name still connecting; ORA-12514 right after a listener restart and recovery once the database re-registers; the app service missing after a PDB restart and surviving the next one after SAVE STATE; and the second listener refusing until LOCAL_LISTENER names it. It also asserts ORA-12154, ORA-12541 and ORA-12505 for their cases. If any of that doesn't reproduce, the run fails. Proven on every CI push.

Cause 1: the wrong name

The most common cause, and the easiest to miss because the name looks right. FREEPDB instead of FREEPDB1, orders instead of orders_app, a domain mismatch (the database has a DB_DOMAIN, so its service is freepdb1.example.com, and the client asks for plain freepdb1, or the other way round), or a connect string still pointing at the old database's service name after a migration.

You don’t have to guess which app is asking for what. The listener log records every refused connect with the service the client requested, the client program, and the client host. From the lab:

(CONNECT_DATA=(SERVICE_NAME=FREEPDB)(CID=(PROGRAM=sqlplus)(HOST=...)(USER=oracle)))
  * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=59690)) * establish * FREEPDB * 12514
TNS-12514: listener does not currently know of service requested in connect descriptor

Even on 26ai the log uses the classic text. lsnrctl status prints the log's location. On a busy listener, grep it for 12514 and count by SERVICE_NAME and HOST: the typo and the server it lives on fall out in one command.

Cause 2: the listener was restarted

This is the one that turns a small problem into an outage. A listener knows only what’s been registered with it since it started. Restart it, and for a while it knows nothing:

$ lsnrctl status
...
The listener supports no services

Every service gets ORA-12514 until the database registers again on its own. In the lab that took about a minute, every time: 61 seconds of refused connections for every service, caused entirely by the restart. Restarting the listener to “fix” ORA-12514 makes it worse.

The database can be told to register right now:

ALTER SYSTEM REGISTER;

In the lab, after the same restart, the connection works immediately after this command. Run it in every instance (and every RAC node) whose services that listener serves. lsnrctl reload re-reads listener.ora without a stop/start, but don't count on it keeping the registrations: in the lab it sometimes kept every one and sometimes dropped them all. Follow a reload with ALTER SYSTEM REGISTER too.

Cause 3: the service didn’t come back with the database

The PDB is open. FREEPDB1 connects. The application's service doesn't:

FREEPDB1 is READ WRITE; active services: freepdb1
localhost:1521/FREEPDB1    -> connected
localhost:1521/orders_app  -> ORA-12514: ... Service orders_app is not registered with the listener ...

A service created with DBMS_SERVICE and started by hand is running only until the next time the PDB closes. After a patch, a server reboot or a switchover, the PDB comes back and its default service with it, but nothing starts orders_app. The database is fine. The application is down.

On a single instance, start the service and save the PDB’s state, so it reopens with the same services running:

ALTER SESSION SET CONTAINER = FREEPDB1;
EXEC DBMS_SERVICE.START_SERVICE('orders_app')
ALTER PLUGGABLE DATABASE FREEPDB1 SAVE STATE;

The lab restarts the PDB again after this, and orders_app connects. Under Grid Infrastructure or RAC, define the service in Clusterware instead (srvctl add service -db ... -pdb FREEPDB1 -service orders_app) and it starts with the database. With Data Guard, the same command takes -role PRIMARY, so the application's service runs only where the primary is, and follows it on switchover. That last case is outside what the lab builds.

Cause 4: a listener the database doesn’t register with

A new listener on port 1522, for an application tier or a firewall rule, or a listener moved off 1521. Clients pointed at it get ORA-12514, and its status says why:

$ lsnrctl status LISTENER2
...
The listener supports no services

The database registers only with the listeners it knows about. By default that’s the local listener on port 1521. Any other address has to be in LOCAL_LISTENER (or REMOTE_LISTENER, for RAC and SCAN):

ALTER SYSTEM SET local_listener =
  '(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=db01)(PORT=1522)))'
  SCOPE = BOTH;

In the lab, both services connect through port 1522 right after this; changing the parameter triggers a registration. (The lab uses SCOPE = MEMORY so a rerun starts clean.) On a Grid Infrastructure node, the agent manages LOCAL_LISTENER; register a new listener with srvctl instead of setting it by hand.

The checklist

When ORA-12514 comes in, in this order:

1. Read the full message. The service name and the host and port the client used are in it (newer clients) or in the listener log (all releases).
2. lsnrctl services on that host. Is the service listed? If yes, the client has the wrong name or the wrong listener.
3. SELECT name FROM v$active_services in the database (in the PDB). Is the service running? If not, start it, and make sure it starts next time (SAVE STATE or srvctl).
4. SHOW PARAMETER local_listener. Does it include the address the client is using?
5. ALTER SYSTEM REGISTER. Re-registers everything now. Harmless, instant, and much better than a listener restart.

What teams get wrong

• Restarting the listener first. It refuses every service until the database re-registers, about a minute in the lab. If a restart or a reload really is needed, follow it with ALTER SYSTEM REGISTER.
• Treating ORA-12514 as “the database is down.” It’s a naming or registration problem. The lab’s database was up and open through every case.
• Starting app services by hand. They don’t survive a PDB restart. Use SAVE STATE or Clusterware, and test it by restarting the PDB before the patch weekend does it for you.
• Giving a PDB name as a SID. That’s ORA-12505, not ORA-12514. PDBs are reached by service name: /FREEPDB1, not :FREEPDB1.
• Relying on a default service. The lab’s container has DEFAULT_SERVICE_LISTENER set, so a connect string with no service name quietly lands in the CDB root. Without that parameter it fails with ORA-12504. Neither is where the application should be; always name the service.

Frequently asked questions

What does ORA-12514 TNS:listener does not currently know of service requested in connect descriptor mean?

It means the client reached a listener, and the listener has no registered service with the name the client asked for. The listener is up and reachable; the database may be healthy too. Either the client asked for the wrong service name (a typo, a missing or extra domain, an old name after a migration), or the right service isn’t registered with that listener: because the listener was just restarted, the service isn’t running, or the database doesn’t register with that listener. Newer clients word the same error as ‘Cannot connect to database. Service X is not registered with the listener at host … port …’.

How do I fix ORA-12514?

Run lsnrctl services on the database host and check whether the service is listed. If it is, fix the client’s connect string; the listener log records the exact service name each refused client asked for. If it isn’t, check v$active_services in the database: start the service if it’s stopped and make it start automatically (SAVE STATE on a single instance, srvctl under Grid Infrastructure). Check that LOCAL_LISTENER includes the listener’s address. Then run ALTER SYSTEM REGISTER to register everything with the listener immediately.

Why do I get ORA-12514 after restarting the listener?

Because a newly started listener knows no services. The database registers its services again on its own, which took about a minute in the companion lab, and until then every service is refused with ORA-12514. ALTER SYSTEM REGISTER in each instance makes the database register immediately. lsnrctl reload, which re-reads listener.ora without a stop/start, sometimes dropped the registrations in the lab too, so follow it with ALTER SYSTEM REGISTER as well.

What is the difference between ORA-12514, ORA-12505, ORA-12541 and ORA-12154?

They fail at different steps. ORA-12154 means the client couldn’t resolve the alias (in tnsnames.ora or LDAP) and never reached a listener. ORA-12541 means nothing is listening at that host and port. ORA-12505 means the listener doesn’t know the SID requested, often because a PDB name was given as a SID (host:1521:PDB instead of //host:1521/PDB). ORA-12514 means the listener answered but has no service by the requested name.

Why does my PDB’s application service disappear after a restart?

A service created with DBMS_SERVICE and started manually is not started automatically when the PDB opens again. The PDB and its default service come back, so the database looks healthy, but connections to the application service fail with ORA-12514. On a single instance, start the service and run ALTER PLUGGABLE DATABASE … SAVE STATE so the PDB reopens with the same services. Under Grid Infrastructure or RAC, create the service with srvctl add service so Clusterware starts it, with -role PRIMARY for Data Guard.

Does ORA-12514 mean the database is down?

No. ORA-12514 is about registration and naming, not database health. In the companion lab the database was up and the PDB open READ WRITE in every case that produced ORA-12514. If the instance were down, a listener with only dynamic registration would also report ORA-12514, so check both: lsnrctl services for what the listener has, and the instance itself for whether it’s running.

ORA-12514 is a listener telling you exactly what’s wrong: it’s here, and it doesn’t have that name. Before you restart anything, compare what the client asked for (the listener log) with what the listener has (lsnrctl services), and let that point you at the client or the database. Then register, don't restart. It sits with the other Oracle Net settings worth getting right, like network encryption, and with the cutover-weekend surprises like the sequence that wasn't moved. Then prove it the way that ends the argument — with the listener-service lab, where a listener restart refuses every service for a minute and one ALTER SYSTEM REGISTER brings them all back.

Originally published at https://uptimearchitect.com on October 20, 2026.

ORA-12514: The Database Is Up. The Listener Just Doesn’t Know It. was originally published in System Weakness on Medium, where people are continuing the conversation by highlighting and responding to this story.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The fix everyone knows is one line: add a tempfile. It takes a minute, it’s online, and the next run fails again, a little later. So someone adds a bigger tempfile, turns on autoextend, and the report fails a third time, after filling the disk.

ORA-01652 says TEMP ran out. It doesn’t say why. Sometimes TEMP really is too small for legitimate work. Often, one query is asking for far more TEMP than any database could give it, and the error is the only thing standing between you and a wrong answer. This post covers what TEMP is for, how to tell the two cases apart, how to find the query, and why TEMP looks full even when it isn’t. It ends with a lab where giving TEMP ten times the space buys ten times the runtime and the same error, while restoring one join predicate makes the same report need no TEMP at all.

What TEMP is for

Sorts, hash joins, DISTINCT, GROUP BY, analytic functions and index builds all need a work area. Oracle gives each one memory from the PGA first. When the work area outgrows its share of memory, the operation spills to the temporary tablespace: it writes partial results to TEMP, then reads them back to finish.

Spilling is normal; a big nightly batch or an index rebuild is supposed to use TEMP. ORA-01652 is raised when a spilling operation asks for another extent and the temporary tablespace has none left to give, and can’t grow. The statement fails and its TEMP is released.

So the question is never just “is TEMP big enough?” It’s “is this operation’s demand reasonable?” A sort of 200,000 rows that needs 40 MB of TEMP is reasonable. A sort that needs 8 GB to produce 50,000 rows is a bug.

The classic runaway

Here is the report as it shipped:

SELECT DISTINCT c.name, c.region, o.order_id, o.amount
FROM   orders o, customers c
WHERE  o.status = 'OPEN';

A line went missing in an edit: AND o.customer_id = c.customer_id. Without it, there is no join condition, so Oracle pairs every open order with every customer, a Cartesian product. The report suddenly showed each order thousands of times, so someone added DISTINCT to "remove the duplicates."

Now the database has to deduplicate the whole product. With 50,000 open orders and 2,000 customers, that’s 100 million rows into a HASH UNIQUE, far beyond any PGA work area. It spills, and keeps spilling until TEMP is gone.

And here’s the part that makes adding space the wrong fix: the rows aren’t actually duplicates. Each customer name is different, so every one of the 100 million combinations is distinct. Given enough TEMP, the report would finish, and return 100 million rows instead of 50,000. ORA-01652 is the only thing stopping a wrong report from shipping.

ORA-01652 means a spilling sort or hash asked TEMP for more space than it had. Before adding space, check whether the demand is reasonable. A legitimate big operation needs more TEMP; a runaway query, like a Cartesian join behind a DISTINCT, will fill any amount of TEMP and needs its SQL fixed.

Prove it

The lab runs Oracle Database Free in a container. It creates a 32 MB temporary tablespace that cannot autoextend and assigns it to an application user. That user owns ORDERS (200,000 rows, 50,000 of them OPEN) and CUSTOMERS (2,000 rows). Then it runs the broken report three ways:

TEMP            report                     result            elapsed   TEMP used
32 MB           join predicate missing     ORA-01652          1.2 s       31 MB
320 MB (10x)    join predicate missing     ORA-01652 again   10.7 s      318 MB
32 MB           join predicate restored    50,000 rows        0.1 s        0 MB

Ten times the space bought ten times the runtime, then the same error. (Elapsed times depend on the hardware; the lab checks the outcomes, not the clock.) A hundred times the space would buy a hundred times the runtime. With the join predicate back, and DISTINCT left in place to show that the join alone is the fix, the report returns its 50,000 rows in a tenth of a second without touching TEMP.

The plan says so before anything runs. EXPLAIN PLAN on the broken report:

| Id  | Operation               | Name      | Rows  | Bytes |TempSpc|
---------------------------------------------------------------------
|   3 |    HASH UNIQUE          |           |  200M |  6484M|  8445M|
|   4 |     MERGE JOIN CARTESIAN|           |  200M |  6484M|       |
|   5 |      TABLE ACCESS FULL  | ORDERS    |  100K |  1660K|       |
|   6 |      BUFFER SORT        |           |  2000 | 34000 |       |
|   7 |       TABLE ACCESS FULL | CUSTOMERS |  2000 | 34000 |       |

MERGE JOIN CARTESIAN and a TempSpc estimate of 8,445M, about 8 GB of TEMP, for a report that should return 50,000 rows. (The optimizer estimates twice the real row count because, without a histogram, it assumes half the orders are OPEN. That doesn't change the conclusion.) Reading an execution plan for TempSpc and Cartesian joins takes seconds, and it would have caught this before the report ever ran.

Don’t take my word for it — run it. The temp-space lab runs the broken report on 32 MB of TEMP and asserts ORA-01652. It asserts the Cartesian join in the plan, then adds a 288 MB tempfile online and asserts the same error again, after catching the report live in V$TEMPSEG_USAGE. Finally it drops the extra tempfile and asserts that the corrected report returns 50,000 rows using no TEMP. If any of that doesn't reproduce, the run fails. Proven on every CI push.

How to find the query

Read the whole message. Recent releases put the culprit in the error itself. The lab’s Oracle Database Free (26ai) says grow rather than extend, and names the SQL ID and how much TEMP the session held:

ORA-01652: unable to grow temp segment in tablespace TEMP_SMALL by 1MB during operation
with SQL ID : 249gkzhhu3cdk, temp space used by session : 31 (MB)

The same line goes to the alert log. On 19c the message names only the tablespace, so you have to catch the statement another way.

Live: V$TEMPSEG_USAGE. While the statement is running, this view shows who holds TEMP and for which SQL:

SELECT s.sid, s.username, u.sql_id_tempseg, u.segtype,
       ROUND(u.blocks * t.block_size / 1024 / 1024) AS temp_mb
FROM   v$tempseg_usage u
JOIN   v$session s ON s.saddr = u.session_addr
JOIN   dba_tablespaces t ON t.tablespace_name = u.tablespace
ORDER  BY u.blocks DESC;

Use SQL_ID_TEMPSEG, not SQL_ID. In the lab, watching the report from a second session, SQL_ID pointed at SQL*Plus's own login query ( SELECT DECODE(USER, 'XS$NULL', ...), while SQL_ID_TEMPSEG was the report that was actually holding the TEMP. The other catch: once the statement fails, its TEMP is released and the row is gone. In the lab, V$TEMPSEG_USAGE is empty a second after the error.

After the fact: ASH. Active Session History samples every active session once a second, and each sample records TEMP_SPACE_ALLOCATED. The statement that filled TEMP stands out:

SELECT sql_id, COUNT(*) AS samples,
       ROUND(MAX(temp_space_allocated) / 1024 / 1024) AS peak_temp_mb
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
AND    temp_space_allocated > 0
GROUP  BY sql_id
ORDER  BY peak_temp_mb DESC;

In the lab, ASH caught 249gkzhhu3cdk peaking at over 300 MB of TEMP. For older incidents, DBA_HIST_ACTIVE_SESS_HISTORY has the same column, and an AWR report shows the top SQL for the window. (ASH and AWR are part of the Diagnostics Pack on Enterprise Edition.)

Then look at its plan. With the SQL ID, DBMS_XPLAN.DISPLAY_CURSOR (if the cursor is still in the shared pool) or EXPLAIN PLAN on the statement text tells you whether the demand makes sense. Compare the TempSpc and row estimates with what the statement should return.

Why TEMP looks full when it isn’t

Right after the failure, the lab looks at the 32 MB tablespace through four views:

V$TEMPSEG_USAGE        0 rows        the session that caused it is gone
V$TEMP_SPACE_HEADER    32 MB used, 0 MB free
V$SORT_SEGMENT         0 MB in use
DBA_TEMP_FREE_SPACE    31 MB free of 32 MB

Oracle doesn’t hand TEMP extents back to the files when a sort finishes. It keeps them allocated in the sort segment and gives them to the next operation that needs them, which is faster than allocating fresh ones. V$TEMP_SPACE_HEADER keeps counting those extents as used after the sort ends, so a monitoring check built on it reports TEMP at 100% all day after the first big sort. That isn't a leak, and it isn't a reason to add space. V$SORT_SEGMENT ( USED_BLOCKS) and DBA_TEMP_FREE_SPACE show what's actually free. Alert on those.

The fix

If the SQL is wrong, fix the SQL. A missing join predicate, a DISTINCT hiding duplicates, a join on the wrong column, a plan that hash-joins two huge row sources because statistics are stale: these need a code or statistics fix. TEMP is just where the damage shows up.

If the work is legitimate, add space online. Big index builds, a nightly aggregation, and more concurrent users than TEMP was sized for are real reasons TEMP is too small:

-- Smallfile temporary tablespace: add a tempfile
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/PROD/temp02.dbf' SIZE 8G;

-- Bigfile temporary tablespace (one file only): resize it
ALTER DATABASE TEMPFILE '/u01/oradata/PROD/temp01.dbf' RESIZE 16G;

Both are online, and so is ALTER TABLESPACE ... DROP TEMPFILE once nothing is using the file; the lab adds a 288 MB tempfile and drops it again without a restart. A bigfile tablespace holds exactly one file, so ADD TEMPFILE fails with ORA-32771 there. The lab's 26ai database creates new tablespaces as bigfile unless told otherwise; check DEFAULT_TBS_TYPE in DATABASE_PROPERTIES on yours.

Contain the damage.

• Cap autoextend. An autoextending tempfile without MAXSIZE doesn't fix a runaway query. It lets the query fill the filesystem instead of the tablespace, and then everything on that filesystem fails too.
• Separate temporary tablespaces. Give a reporting or batch user its own temporary tablespace (the lab’s user hasTEMP_SMALL). Then one application's runaway query can't starve every other session's sorts.
• Size from evidence. The peak of DBA_HIST_ACTIVE_SESS_HISTORY.TEMP_SPACE_ALLOCATED summed across sessions, over a few busy weeks, tells you what your legitimate workload actually needs.

What teams get wrong

• Adding space first, asking later. It’s the right fix maybe half the time. Check the statement’s plan before you add the second tempfile, not after the third.
• Turning on unlimited autoextend. It converts a failed report into a full filesystem. Use MAXSIZE.
• Trusting V$TEMP_SPACE_HEADER. It shows extents the sort segment has cached, not space in use. A TEMP that "is always 100% full" is usually fine.
• Looking for the culprit too late, or in the wrong column. V$TEMPSEG_USAGE empties the moment the statement fails; read the full error text, or go to ASH. While it's running, trustSQL_ID_TEMPSEG, not SQL_ID.
• Hiding duplicates with DISTINCT. Unexpected duplicates are a symptom of a wrong join. DISTINCT makes the report look right on small data and turns into a TEMP bomb on real data.
• Confusing it with a permanent tablespace. ORA-01652 ... in tablespace USERS (or any non-temporary tablespace) is a different problem: aCREATE TABLE AS SELECT, index build or MOVE ran out of room while building its new segment. The tablespace named in the message tells you which case you have.

Frequently asked questions

What does ORA-01652 unable to extend temp segment mean?

It means an operation that spills to disk, such as a sort, hash join, DISTINCT, GROUP BY or index build, asked the temporary tablespace for another extent and none was available, and the tablespace could not grow. The statement fails and its temporary space is released. The error says that TEMP ran out, not why: either the temporary tablespace is genuinely too small for legitimate work, or one statement is demanding far more TEMP than it should, often because of a missing join predicate or a bad plan.

Should I just add a tempfile to fix ORA-01652?

Only after checking the statement that caused it. If the work is legitimate, such as a large index build, a nightly batch or more concurrent sessions, adding a tempfile (or resizing the tempfile of a bigfile tablespace) is the right fix and it is online. If the statement is a runaway, such as a Cartesian join behind a DISTINCT, more space only moves the failure. In the companion lab, giving TEMP ten times the space made the same report fail again after ten times the runtime, while restoring one join predicate made it return 50,000 rows using no TEMP.

How do I find which query caused ORA-01652?

Recent Oracle releases include the SQL ID and the session’s temp usage in the error message and the alert log. On 19c, which names only the tablespace, query V$TEMPSEG_USAGE joined to V$SESSION while the statement is running (use its SQL_ID_TEMPSEG column, which identifies the statement that owns the temp segment; SQL_ID can show a different statement the session parsed), or look in Active Session History afterwards: V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY record TEMP_SPACE_ALLOCATED for every sample, so grouping by SQL_ID and taking the maximum shows the statement that filled TEMP. Then check its execution plan for a Cartesian join or a TempSpc estimate far larger than the result should need.

Why does my temporary tablespace always show 100% used?

Because Oracle keeps temporary extents allocated in the sort segment after a sort finishes and reuses them for the next operation, rather than freeing them back to the tempfiles. V$TEMP_SPACE_HEADER counts those cached extents as used, so it can show TEMP as full long after the sorts that used it have finished. V$SORT_SEGMENT (USED_BLOCKS versus FREE_BLOCKS) and DBA_TEMP_FREE_SPACE show the space that is actually free, and are the right views to monitor.

Can I add or drop a tempfile without downtime?

Yes. ALTER TABLESPACE temp ADD TEMPFILE … SIZE … is online, and so is ALTER TABLESPACE temp DROP TEMPFILE … once no session is using the file. A bigfile temporary tablespace can hold only one tempfile, so adding a file fails with ORA-32771; resize the existing one with ALTER DATABASE TEMPFILE … RESIZE instead. DATABASE_PROPERTIES.DEFAULT_TBS_TYPE shows whether new tablespaces default to bigfile or smallfile on your database.

Is ORA-01652 on a permanent tablespace the same problem?

No. When the message names a permanent tablespace such as USERS rather than a temporary one, the space was needed to build a new permanent segment: a CREATE TABLE AS SELECT, an index build or rebuild, or an ALTER TABLE MOVE, which create their new segment as temporary extents in the target tablespace until they finish. The fix is space in that tablespace, or a smaller operation, not a bigger TEMP.

ORA-01652 is easy to make go away for a day and hard to make go away for good, because the obvious fix treats the symptom. Before you add space, read the whole error, find the statement, and look at its plan. If the demand is legitimate, add a tempfile online and cap its growth. If it’s a Cartesian join behind a DISTINCT, no amount of TEMP will help, and enough of it would let a wrong report finish. It belongs with the other errors that freeze or fail work for a reason the message doesn't spell out, like ORA-01555 and ORA-00257. Then prove it the way that ends the argument - with the temp-space lab, where ten times the TEMP buys ten times the runtime and the same error, and one restored join needs none.

Originally published at https://uptimearchitect.com on October 17, 2026.

ORA-01652: Unable to Extend Temp Segment. Don’t Just Add Space. was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.

Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete

Nothing is broken, and no query is slow. The database is waiting for itself: the online redo logs are too small for the rate at which the workload changes data, so the log writer keeps running out of logs it is allowed to reuse. This post covers what that message means, why it freezes every writer at once, how to spot it before users do, and how to fix it online. It ends with a lab where the same update takes 14.1 seconds on two 20 MB redo logs, 11.9 of them waiting, and 1.9 seconds after the fix.

How the redo log circle works

Every change in an Oracle database is first written as redo to the online redo logs, a small, fixed set of files ( groups) that the log writer (LGWR) fills one after another in a circle. When the current group is full, LGWR switches to the next one, and when it reaches the last group it wraps around and reuses the first.

Reusing a group is only safe once nothing still needs the redo in it for crash recovery. The redo protects changes to data blocks that may still exist only in memory. So before a group can be overwritten, the checkpoint that covers it must finish: the database writer (DBWR) has to write every dirty block whose changes are recorded in that group to the datafiles. Until that happens, V$LOG shows the group as ACTIVE, meaning still needed for recovery, and LGWR cannot touch it.

Normally the circle is big enough that DBWR finishes long before LGWR comes back around, and the next group is always INACTIVE and ready.

Why it freezes everyone

When the logs are small relative to the write rate, LGWR fills the whole circle faster than DBWR can complete the checkpoints. It comes back to a group that is still ACTIVE, and it has nowhere to write. The alert log records it as "cannot allocate new log ... Checkpoint not complete".

LGWR is a single point of serialisation. Until DBWR frees that group, no session in the database can generate redo: no insert, no update, no delete, no commit. They all wait on the same event, log file switch (checkpoint incomplete), and then all resume at once. That's the stutter. It's not one slow query; it's the whole instance pausing every time the log writer laps the checkpoint.

LGWR writes redo into a circle of online log groups. Before it can reuse a group, DBWR must finish the checkpoint covering it. With two 20 MB groups and a heavy write burst, LGWR comes back around to a group that is still ACTIVE, and every session that needs to write redo waits until DBWR frees it. With groups large enough to absorb the burst, the next group is always INACTIVE and nobody waits.

This is a close cousin of ORA-00257, archiver stuck. There, a log group can’t be reused because the archiver hasn’t copied it yet, and the wait is log file switch (archiving needed). Here it's because the checkpoint hasn't finished. Same circle, same freeze, different fix.

Prove it

The lab uses the community Oracle Database Free container image, which trims the redo logs to two groups of 20 MB each to keep the image small: exactly the layout that causes this. It loads a table of 100,000 rows of about 1 KB, then runs one statement that rewrites every row and commits, a little over 200 MB of redo. It measures the elapsed time, the waits from V$SYSTEM_EVENT, the switches from V$LOG_HISTORY, and the Checkpoint not complete lines in the alert log:

redo logs              elapsed   switches   waits (time waited)   alert lines
2 x 20 MB (image)       14.1 s         12        12 (11.9 s)              12
3 x 512 MB               1.9 s          0         0                        0

On the default logs, 84% of the update’s elapsed time was waiting for a redo log to become reusable: 11.9 of 14.1 seconds. The statement itself was never the problem. After the logs were resized, the identical update ran in 1.9 seconds with no switches and no waits.

Elapsed times depend on the hardware and vary from run to run; on my machine the small-log update took between 8 and 14 seconds. The waits and the alert-log lines appear every time, and disappear every time after the resize.

Don’t take my word for it — run it. The checkpoint-incomplete lab runs the burst on the default 2 x 20 MB logs and asserts that log file switch (checkpoint incomplete) waits and Checkpoint not complete alert-log lines appear. It then resizes the logs online and asserts the same burst produces zero of both. If the stall doesn't reproduce, or the fix doesn't remove it, the run fails. Proven on every CI push.

How to spot it

The alert log is the giveaway. Search it for Checkpoint not complete; one occurrence during a once-a-year data load is noise, but dozens a day mean the logs are undersized for your real workload.

The wait event. log file switch (checkpoint incomplete) shows up in V$SYSTEM_EVENT, in AWR's Top Events and in ASH. In an AWR report it's often near the top during batch windows, and it's easy to misread as an I/O problem, because the time is spent waiting on DBWR's writes.

How often the logs switch. V$LOG_HISTORY has one row per switch:

SELECT TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24') AS hour,
       COUNT(*)                                             AS switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 7
GROUP  BY TRUNC(first_time, 'HH24')
ORDER  BY 1;

A common rule of thumb is a few switches per hour at peak, roughly one every 15 to 20 minutes. The lab’s update switched 12 times in 14 seconds. If your busiest hours show switches per minute, the logs are far too small for that workload, whether or not you’ve seen the alert yet.

Oracle’s own sizing advice. V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE suggests a redo log size in megabytes, but only when FAST_START_MTTR_TARGET is set. With it at 0, as on the lab database, the column is empty. In the lab, with a 60-second target, it suggested 827 MB. Treat it as a starting point, not a law: it's derived from the recovery target, not from your peak write rate.

The fix

You can’t resize a redo log file in place. Instead, you add new groups at the size you want, move onto them, and drop the old ones, all online:

-- 1. Add bigger groups (use your own paths, and two members each on separate disks in production)
ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/oradata/PROD/redo03.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/PROD/redo04.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oradata/PROD/redo05.log') SIZE 2G;
-- 2. Switch until one of the new groups is CURRENT, then checkpoint
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
-- 3. Wait until the old groups show INACTIVE in V$LOG, then drop them
SELECT group#, bytes / 1024 / 1024 AS mb, status FROM v$log;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;

• Order matters. A CURRENT group can't be dropped, and neither can an ACTIVE one, because it's still needed for recovery. Switch off it, checkpoint, and wait forINACTIVE.
• Dropping a group doesn’t delete its file. Remove the old files at the operating-system level afterwards (with Oracle Managed Files, the database does it for you).
• Size for the peak, not the average. The goal is that, at your busiest, a log lasts long enough for DBWR to finish the checkpoint long before LGWR needs it again. In the lab, 512 MB groups hold the entire burst, so there is no switch at all. In production, aim for that rule of thumb of a few switches an hour at peak.
• More groups help too. A third or fourth group gives DBWR more time before LGWR comes back around. Two groups is the bare minimum Oracle allows, and it leaves no slack.
• All groups the same size. Mixed sizes make switch timing unpredictable and complicate standby redo logs.

The other lever is DBWR itself: if it can’t keep up because the storage is slow or asynchronous I/O is off, checkpoints take longer at any log size. (The lab container runs with FILESYSTEMIO_OPTIONS=none, which doesn't help DBWR either.) But undersized logs are far more common, and resizing them is a five-minute, fully online change.

What teams get wrong

• Treating it as a storage problem. The waits look like I/O, so the first instinct is faster disks. Faster writes help DBWR a little; logs sized for the workload remove the wait entirely.
• Never changing the default. Many databases still run on the redo logs they were created with, sized for the workload of the day they were built, or trimmed for a small footprint the way the lab’s container image trims them to two 20 MB groups. Fine for a laptop, not for a batch window. Check yours.
• Forgetting the standby. With Data Guard, standby redo logs should be the same size as the online redo logs, with one more group per thread. Resize the online logs and leave the standby redo logs small, and the standby can’t use them for real-time apply; it falls back to applying from archived logs. Resize both.
• Confusing it with the archiver. log file switch (archiving needed) is ORA-00257's wait: the archive destination is full or slow. Bigger logs don't fix that one; freeing the destination does.
• Fearing longer recovery, or ignoring it. With FAST_START_MTTR_TARGET at 0, how far the checkpoint lags is tied to the size of the smallest log, so bigger logs can mean more redo to replay after a crash. When you enlarge the logs, setFAST_START_MTTR_TARGET (say, 60 to 300 seconds) and the database paces its checkpoint writes to keep recovery time bounded. Undersized logs cost you every busy day; properly sized ones cost a little disk.
• Dropping the wrong group. Run SELECT group#, status FROM v$log before every DROP LOGFILE. A CURRENT orACTIVE group can't be dropped, and in a hurry it's easy to type the wrong number.

Frequently asked questions

What does ‘Checkpoint not complete’ mean in the Oracle alert log?

It means the log writer (LGWR) filled the current online redo log and wanted to switch to the next group in the circle, but that group was still ACTIVE: the checkpoint covering it had not finished, so the database writer (DBWR) had not yet written all the dirty blocks whose changes are recorded in that log. The log cannot be overwritten until that happens, so LGWR waits, and while it waits no session can generate redo. The usual cause is online redo logs that are too small for the database’s write rate, so LGWR laps the checkpoint during busy periods.

What is the ‘log file switch (checkpoint incomplete)’ wait event?

It is the wait that sessions experience when they need to write redo but the log writer cannot switch to the next online redo log, because that log is still needed for crash recovery until a checkpoint completes. Every session that inserts, updates, deletes or commits waits on it at the same time, which is why it shows up as the whole database stalling rather than one slow query. In the companion lab, a 100,000-row update spent 11.9 of its 14.1 seconds on this wait with two 20 MB redo logs, and zero seconds after resizing them.

How do I resize Oracle online redo logs?

Online redo log files cannot be resized in place. Add new log groups at the desired size with ALTER DATABASE ADD LOGFILE GROUP n … SIZE, run ALTER SYSTEM SWITCH LOGFILE until one of the new groups is CURRENT, run ALTER SYSTEM CHECKPOINT, wait until V$LOG shows the old groups as INACTIVE, then drop them with ALTER DATABASE DROP LOGFILE GROUP n and delete their files at the operating-system level (Oracle Managed Files are removed automatically). The whole procedure is online. Keep all groups the same size, and in a Data Guard configuration resize the standby redo logs to match.

How big should Oracle redo logs be?

Big enough that, at peak load, a log lasts long enough for the checkpoint covering the previous logs to finish well before the log writer needs them again. A common rule of thumb is a few log switches per hour at the busiest time, roughly one every 15 to 20 minutes. Check V$LOG_HISTORY for switches per hour; switches per minute mean the logs are far too small. V$INSTANCE_RECOVERY.OPTIMAL_LOGFILE_SIZE also suggests a size in megabytes, but only when FAST_START_MTTR_TARGET is set. Use at least three groups, all the same size.

Is ‘Checkpoint not complete’ the same as ORA-00257 archiver error?

No, although both freeze every session that writes. With ‘Checkpoint not complete’, the next online redo log cannot be reused because its checkpoint has not finished, and sessions wait on log file switch (checkpoint incomplete); the fix is usually larger or more redo log groups. With ORA-00257, the database is in ARCHIVELOG mode and the next log cannot be reused because the archiver has not been able to copy it, typically because the archive destination or Fast Recovery Area is full; sessions wait on log file switch (archiving needed), and the fix is to free or enlarge the archive destination.

Do larger redo logs make crash recovery slower?

Not in a way that should stop you from sizing them properly. Crash recovery time depends on how much redo must be applied since the last checkpoint position, and modern Oracle uses incremental checkpointing to write dirty blocks continuously rather than only at log switches. With FAST_START_MTTR_TARGET at 0, how far that checkpoint may lag is tied to the size of the smallest redo log, so larger logs can mean more redo to replay. Setting FAST_START_MTTR_TARGET makes the database pace its checkpoint writes to keep the estimated recovery time within the target, so set it when you enlarge the logs. Undersized logs, on the other hand, stall every writer on every busy day.

Redo sizing is one of those settings that is decided once, at database creation, and then forgotten until the workload outgrows it. The symptoms point everywhere else: the wait events look like I/O, the batch looks slow, and the SQL looks guilty. Check the alert log for Checkpoint not complete, count your log switches per hour, and if the logs are lapping the checkpoint, give them room: add bigger groups, switch, checkpoint, drop the old ones, and resize the standby redo logs to match. Then prove it the way that ends the argument - with the checkpoint-incomplete lab, where the same update drops from 14.1 seconds, 11.9 of them waiting, to 1.9.

Originally published at https://uptimearchitect.com on October 13, 2026.

That last sentence is the clue. The statement uses a bind variable, which is the right thing to do, and the cursor for it holds one plan. That plan was chosen for whichever value happened to arrive first. On a column where values are wildly unevenly distributed, that’s a plan built for one kind of caller and handed to every other. This post is how that happens, why Oracle’s safety net for it, adaptive cursor sharing, doesn’t always catch it, how to see it in the cursor cache, and how to fix it, with a lab that reproduces it on demand.

Bind peeking: the first caller picks the plan

When a statement with a bind variable is hard-parsed, the optimizer peeks at the actual bind value and costs the plan as if that value were a literal. That’s usually good: a plan built for a real value beats a plan built for an average. The catch is what happens next. The finished cursor goes into the shared pool and every later execution reuses it, whatever value it binds. The plan is decided once, by the first caller.

On a column with evenly spread values, that doesn’t matter: one value costs about the same as another. It matters on a skewed column, where a few values are rare and one is everywhere: a status code where 99.9% of rows are CLOSED, a country column dominated by one country, a flag that's almost always N. If the column has a histogram, the optimizer knows about the skew and builds very different plans for the rare value and the common one. Peeking means it builds one of them, and everyone gets that one.

The classic shape is a join. For a rare value, a few hundred driving rows, the best plan is nested loops: for each order, look up its customer by primary key. For the common value, hundreds of thousands of rows, the best plan is a hash join: read both tables once and match them in memory. Nested loops for the common value means one index probe per row, hundreds of thousands of times.

A bind-variable statement is optimized once, for the first value it sees, and the cursor is reused for every value after. On a skewed column with a histogram, a rare first value produces a nested-loops plan that is right for a few rows; the common value then inherits it and pays for one index probe per row. Adaptive cursor sharing can split the cursor into one plan per kind of value, but only once it has seen executions that differ enough; until then every caller gets the first caller’s plan.

Adaptive cursor sharing: the safety net, with holes

Oracle has known about this since 11g, and adaptive cursor sharing (ACS) is its answer. A cursor that peeked a bind on a column with a histogram is marked bind-sensitive. Oracle then watches its executions, sorting each one into a small number of buckets by how much work it did. If executions with different bind values land in clearly different buckets, the cursor becomes bind-aware: from then on, each new value is checked against the existing child cursors, and a value that needs a different plan gets its own child cursor with its own plan.

That works well when the difference is visible. The hole is in “clearly different”. If the rare value’s first execution and the common value’s executions land in the same bucket, ACS sees nothing to react to, and the cursor stays exactly as it was. And whether you’re protected can depend on the order in which values arrive after each restart, shared pool flush, or statistics refresh, which nobody controls.

Prove it

The lab builds ORDERS with 200,000 rows and a skewed STATUS, 200 OPEN and 199,800 CLOSED, with a frequency histogram on the column, and joins it to a 1,000,000-row CUSTOMERS table. It flushes the shared pool, runs the statement once with'OPEN', then five times with 'CLOSED', each in a fresh session, measuring session logical reads from V$MYSTAT and the child cursor each execution ran:

>> Fresh shared pool. The SAME statement, bound:  ... where o.status = :s
   OPEN    rows=200     logical reads=2624     child cursor=0  plan=NESTED LOOPS
   CLOSED  rows=199800  logical reads=603391   child cursor=0  plan=NESTED LOOPS
   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS
   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS
   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS
   CLOSED  rows=199800  logical reads=603354   child cursor=0  plan=NESTED LOOPS

The optimizer peeked 'OPEN', estimated 199 rows from the histogram, and chose nested loops, which is right for 200 rows. Every'CLOSED' run then reused child cursor 0 and its nested loops: 603,354 logical reads, five times in a row. Adaptive cursor sharing never marked the cursor bind-aware. The first 'OPEN' execution, including its hard parse, and every'CLOSED' execution fell into the same middle bucket, so the safety net never saw a difference. (The plan was even an adaptive plan, with a statistics collector that can switch join methods, but an adaptive plan settles on its first execution and the settled plan is what gets reused.)

Give 'CLOSED' its own plan and the cost collapses:

>> The fix for a skewed, low-cardinality column: the predicate as a literal -> one cursor + plan per value
   CLOSED  rows=199800  logical reads=34716    child cursor=0  plan=HASH JOIN
   OPEN    rows=200     logical reads=806      child cursor=0  plan=NESTED LOOPS

34,716 logical reads instead of 603,354: the same rows, 17 times less work, because the plan was built for the value it runs. And'OPEN' keeps its cheap nested loops at 806.

Don’t take my word for it — run it. The bind-peeking lab builds the skewed tables on Oracle Database Free, peeks the rare value, and asserts that every common-value run inherits nested loops on the same cursor at 10 times or more the work of a hash join, without adaptive cursor sharing splitting it. Then it gives each value its own plan and asserts the common value drops to 10% or less of that work. If the trap stops reproducing or the fix stops working, the run fails. Proven on every CI push.

See it in your own system

Start from the statement, not the plan. A query whose cost swings by value shows up as high variance in buffer gets per execution for one SQL_ID. Then look at its cursors:

-- one row per child cursor: which ones are bind-sensitive / bind-aware, and what each costs
SELECT child_number, plan_hash_value, is_bind_sensitive, is_bind_aware,
       executions, ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec
FROM   v$sql
WHERE  sql_id = '&sql_id'
ORDER  BY child_number;

• Bind-sensitive Y, bind-aware N, one child, huge gets per execution: the lab's situation. The cursor knows the bind matters, but it never split.
• Several children with different plan hash values, bind-aware Y: ACS is doing its job.
• V$SQL_CS_HISTOGRAM shows how its executions were bucketed; all executions in one bucket means ACS has nothing to react to.

To see which value the plan was built for, ask for the peeked binds:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', 0, 'TYPICAL +PEEKED_BINDS'));

In the lab that prints :S ... 'OPEN' above a nested-loops plan, the whole diagnosis in one screen. In AWR, the same statement appearing with more than one plan hash value over time, with very different elapsed times, is the historical fingerprint: the plan changes whenever the cursor is rebuilt with a different first value.

The fix

Pick by how the column behaves. In order of preference:

• For a skewed, low-cardinality column, use a literal for that predicate. A status code with five values makes five cursors, each with the right plan. That’s no hard-parse storm: the storm comes from literal values with high cardinality, like IDs. Keep binds for everything else in the statement. This is the exception to “bind everything” that thebind variables post ends on, and the lab’s fix.
• Split the query in the application when it already knows which kind of value it has: a separate statement for the “open orders” screen and the “history” screen. Two statements, two plans, no guessing.
• A SQL plan baseline with more than one accepted plan. Baselines cooperate with adaptive cursor sharing: accept the nested-loops plan and the hash-join plan, and each value can use the one that fits. SeeSQL plan management.
• Remove the histogram, if one plan is genuinely acceptable for every value. Without it the optimizer stops distinguishing values and picks one middle-of-the-road plan. It’s a legitimate choice when no value is catastrophic, but a bad one when, as here, the wrong plan costs 17 times the work.

What not to do: don’t “fix” it by flushing the shared pool or purging the cursor when it goes bad. That just rolls the dice again on which value arrives first, and the problem returns on its own schedule.

What teams get wrong

• Blaming the data or the database for “random” slowness. It isn’t random: it’s the plan built for whichever value arrived first, and it changes when the cursor is rebuilt.
• Testing by hand. Paste the statement into a SQL tool with the slow value typed in and it’s different SQL text, so it gets itsown hard parse and the right plan, and it’s fast. The problem only shows on the shared, bind-variable cursor the application uses.
• Trusting adaptive cursor sharing to handle it. It helps when executions differ enough to land in different buckets, and quietly does nothing when they don’t, as the lab shows five times in a row.
• Flushing the shared pool as the fix. It resets the lottery; it doesn’t change the odds.
• Disabling bind peeking database-wide. It’s a hidden parameter, and switching it off trades one wrong plan for a plan built for no real value at all, for every statement in the database.
• Turning a whole application to literals to escape it. That’s the hard-parse storm all over again. Use literals only for the skewed, low-cardinality predicate.

Frequently asked questions

What is bind peeking in Oracle?

Bind peeking is the optimizer’s behavior of looking at the actual value of a bind variable when it hard-parses a statement, and optimizing the plan as if that value were a literal. It produces a plan suited to a real value rather than to an average. The resulting cursor is then stored in the shared pool and reused by every later execution of the same statement, regardless of the value those executions bind. So the plan is effectively chosen by whichever value happened to arrive first when the cursor was built. On columns with evenly distributed values this rarely matters; on skewed columns with histograms it can make the same SQL fast for one value and very slow for another.

Why is the same Oracle query fast for one value and slow for another?

Usually because the statement uses a bind variable on a skewed column and the cursor’s single plan was built for a different kind of value. If the first execution peeked a rare value, the optimizer may have chosen nested loops or an index-driven plan that is ideal for a few rows; every later execution reuses that plan, and a common value matching hundreds of thousands of rows then pays for one index probe per row. In the companion lab the same join costs 2,624 logical reads for a rare value and 603,354 for the common value on the inherited plan, versus 34,716 with its own hash-join plan. Running the query by hand with the slow value often looks fast, because a manual run with different text gets its own hard parse and the right plan.

What is adaptive cursor sharing?

Adaptive cursor sharing, introduced in Oracle 11g, lets a statement with bind variables have more than one plan. A cursor that peeked a bind on a column with a histogram is marked bind-sensitive, and Oracle monitors how much work its executions do, sorting them into buckets. If executions with different bind values land in clearly different buckets, the cursor is marked bind-aware, and new bind values are then checked against the existing child cursors; a value that needs a different plan gets a new child cursor with its own plan. You can see it in V$SQL (IS_BIND_SENSITIVE, IS_BIND_AWARE and multiple child cursors) and in V$SQL_CS_HISTOGRAM.

Why didn’t adaptive cursor sharing fix my plan?

Because adaptive cursor sharing only reacts when it sees executions that differ enough to fall into different workload buckets. If the rare value’s first execution and the common value’s executions land in the same bucket, the cursor stays bind-sensitive but never becomes bind-aware, and every execution keeps using the original plan. In the companion lab the first execution with the rare value, including its hard parse, and all five executions with the common value fell into the same bucket, so the cursor never split even though the common value did 17 times the necessary work each time. Whether a given workload is protected can also depend on the order in which values arrive after a restart, a shared pool flush, or a statistics refresh.

How do I find which bind value a plan was built for?

Use DBMS_XPLAN.DISPLAY_CURSOR with the PEEKED_BINDS format option, for example SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’, child_number, ‘TYPICAL +PEEKED_BINDS’)). The output includes a Peeked Binds section showing the value the optimizer saw when it built that child cursor’s plan. Combine it with V$SQL for the same SQL_ID: one row per child cursor, its plan hash value, whether it is bind-sensitive and bind-aware, and buffer gets per execution. A single child with huge gets per execution and a peeked value from the rare end of a skewed column is the bind-peeking signature.

Should I use literals instead of bind variables to avoid bind peeking problems?

Only for the specific predicate on a skewed, low-cardinality column, such as a status code or a flag with a handful of values. Each distinct literal gets its own cursor and its own plan, and with only a few distinct values that means only a few cursors, which is no burden on the shared pool. Keep bind variables for high-cardinality values like IDs, names, and dates: using literals there causes a hard-parse storm, with thousands of nearly identical cursors, shared pool pressure, and latch contention. The rule is bind by default, literal by exception, and the exception is low-cardinality skew.

Does flushing the shared pool fix a bad bind-peeked plan?

Only by accident, and only temporarily. Flushing the shared pool, or purging the one cursor with DBMS_SHARED_POOL.PURGE, forces a new hard parse, and the new plan will be built for whichever bind value arrives first afterwards. If that is the common value, things look fixed until the next time the cursor is rebuilt with the rare value first; if it is the rare value, nothing changes. It resets the lottery without changing the odds. Fix the cause instead: give the skewed predicate a literal, split the statement in the application, or use a SQL plan baseline with more than one accepted plan.

Should I disable bind peeking?

No. Disabling bind peeking requires a hidden parameter, which should only be changed under Oracle Support’s guidance, and it applies to every statement in the database. Without peeking, the optimizer builds plans using average selectivity rather than any real value, so instead of a plan that is right for some values you get a plan that is right for none of the skewed ones. Address the specific statements that suffer: literals for low-cardinality skewed predicates, separate statements per case, SQL plan baselines, or removing a histogram that does more harm than good.

Bind peeking sits right at the meeting point of two other posts in this series. Bind variables are why the cursor is shared in the first place, and that’s the right default; indexes are why the same predicate deserves an index for one value and a full scan for another. The plan itself is something you can read in the execution plan, the histogram that sets the trap comes from optimizer statistics, and SQL plan management is one way to pin more than one good plan. When one statement is fast for some values and slow for others, look at the peeked bind before anything else, and prove it the way that ends the argument — with the bind-peeking lab, where the same query costs 2,624 reads for one value and 603,354 for another until each value gets its own plan.

Originally published at https://uptimearchitect.com on October 10, 2026.

Same SQL, Fast for One Value, Slow for Another was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.

ORA-00001: unique constraint (NEWSHOP.SYS_C008699) violated on table NEWSHOP.ORDERS columns (ID)

Nothing was lost and nothing was corrupted. Every row was there. The problem was a number that no data check looks at: the sequence that generates new order IDs was still where the initial load left it, days earlier, and it was handing out IDs the source had already used. This post is how that happens, why rehearsals tend to miss it, the check that catches it before go-live, and the fix.

Sequences are objects, not data

An Oracle sequence is a schema object that hands out numbers: ORDER_SEQ.NEXTVAL returns 9,001, then 9,002, and so on. The application uses it for new keys. Crucially, the sequence's current position is part of its definition, not part of any table's data. When you export a sequence, Data Pump writes a CREATE SEQUENCE ... START WITH n statement, where n is where the sequence stood at that moment.

That one fact sets the trap. Many migrations work like this:

1. Initial load, days or weeks before cutover: export and import the whole schema, tables and sequences.
2. The source keeps running. Customers keep placing orders; ORDER_SEQ keeps advancing on the source.
3. Final refresh on cutover night: copy just the table data again, so the target catches up. With Data Pump that isCONTENT=DATA_ONLY with TABLE_EXISTS_ACTION=TRUNCATE; with other tools it's a final catch-up load or replication.

Step 3 is deliberately data-only so it doesn’t touch the target’s objects, grants and code. Sequences are objects, so they are not touched either. The tables now contain every order up to 10,000; the sequence still thinks the next order is 9,001.

The initial load copies the table and the sequence (next = 9,001). The source keeps taking orders up to id 10,000. The final refresh copies table data only, so the target has every row but its sequence never moved. The first insert after go-live asks the sequence for 9,001, which already exists, and fails with ORA-00001. Resetting the sequence past max(id) before go-live prevents it.

Why rehearsals miss it

Two things make this bug unusually good at surviving testing.

• Every data check passes. Row counts, checksums and reconciliation reports compare data. The data is correct. Nothing in a typical validation compares a sequence’s position to the keys in its table.
• Cached sequences hide it. With a cached sequence, Data Pump exports the sequence’s LAST_NUMBER, which is the top of its in-memory cache, not the last value actually used. In the lab's first run, withCACHE 20 on a release that grows sequence caches dynamically, the source had used 9,000 values but the target sequence started at22,221. A rehearsal with a few hundred test orders never reaches that headroom. Production, with days of real orders between initial load and cutover, does.

And when it does fail, it fails noisily but confusingly. A failed insert still consumes a NEXTVAL, so an application that retries walks the sequence forward one collision at a time. In the lab that is 1,000 failed orders before the sequence reaches a free key. Users see intermittent errors that "fix themselves", which is the worst kind to diagnose under cutover pressure.

Prove it

The lab builds a source schema SHOP with 9,000 orders keyed by ORDER_SEQ, then runs the migration exactly as above with expdp/ impdp: a full initial load into NEWSHOP, 1,000 more orders on the source, and a final CONTENT=DATA_ONLY refresh.

initial load    source  9,000 rows, max(id)  9,000 | target  9,000 rows, max(id)  9,000, ORDER_SEQ next 9,001
source drift    source 10,000 rows, max(id) 10,000 | target (unchanged)
final refresh   source 10,000 rows, max(id) 10,000 | target 10,000 rows, max(id) 10,000, ORDER_SEQ next 9,001
check           NEWSHOP.ORDER_SEQ  next=9001  max(ORDERS.ID)=10000  BEHIND by 1000
first insert    ORA-00001: unique constraint violated on table NEWSHOP.ORDERS columns (ID)
fix             ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001
first insert    OK, new id = 10001

Don’t take my word for it — run it. The sequence-cutover lab runs this whole migration on Oracle Database Free with real expdp/ impdp. It asserts that the row counts match after the final refresh, that the pre-go-live check flags the sequence as behind, that the first insert fails with ORA-00001, and that after the fix the same insert succeeds with a key above everything migrated. If any of those stops being true, the run fails. Proven on every CI push.

The check: run it before you open the doors

For every sequence that feeds a key, compare the value it will hand out next with the highest key already in the table. Oracle doesn’t record which sequence feeds which column (unless it’s a column DEFAULT or an identity column), so the mapping belongs in the cutover runbook. The check itself is short:

-- next value each sequence will hand out vs the highest key in the table it feeds
SELECT s.sequence_owner, s.sequence_name, s.last_number AS next_value,
       (SELECT MAX(id) FROM newshop.orders) AS max_key,
       CASE WHEN s.last_number <= (SELECT MAX(id) FROM newshop.orders)
            THEN 'BEHIND' ELSE 'ok' END AS status
FROM   dba_sequences s
WHERE  s.sequence_owner = 'NEWSHOP' AND s.sequence_name = 'ORDER_SEQ';

The lab’s version loops over a list of sequence => table.column pairs, so one run covers every sequence in the application. Any BEHIND row is a guaranteed ORA-00001 waiting for its first insert.

The fix

Move each lagging sequence past the highest existing key:

ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001;   -- max(id) + 1

RESTART START WITH (18c and later) resets the sequence in place, so grants, synonyms and dependent code stay intact. On older releases, either temporarily change INCREMENT BY to the gap, call NEXTVAL once and set it back, or drop and recreate the sequence and re-grant it. For identity columns, Oracle can do the arithmetic for you: ALTER TABLE newshop.orders MODIFY id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) moves the identity's next value past the current maximum.

The durable fix is in the runbook, not the database:

• Make “resync sequences” an explicit cutover step, after the final data load and before go-live, usingmax(key) + 1 (plus a margin if anything else might still write).
• Or re-export sequences with the final data. Include the sequence objects in the final refresh and replace the target’s copies, so they carry the source’s position at cutover time.
• Run the check as a go/no-go gate. A cutover isn’t done when the rows match. It’s done when the sequence check reports noBEHIND.

What teams get wrong

• Treating “row counts match” as “migration verified”. Data checks prove the data. They say nothing about the objects that generate the next row.
• Rehearsing with too little traffic. A cached sequence’s export position includes headroom. If the rehearsal doesn’t generate more keys between initial load and refresh than that headroom, the bug never shows.
• Fixing it by dropping the primary key “temporarily”. That turns a loud error into silent duplicate IDs. Fix the sequence, never the constraint.
• Resetting to exactly max(id). START WITH is the next value handed out, so it must be max(id) + 1.RESTART START WITH max(id) produces one more collision.
• Forgetting sequences that don’t feed a primary key. Invoice numbers, external reference IDs and other unique business keys come from sequences too, and collide the same way.

Frequently asked questions

Why do I get ORA-00001 right after a database migration?

Usually because a sequence on the new database is behind the data. Migrations often load the schema once, including sequences, while the source keeps running, then refresh only the table data at cutover. The tables end up with every row, but each sequence is still at the position it had at the initial load, so its next values are keys that already exist in the table. The first inserts that use those sequences fail with ORA-00001 unique constraint violated. Nothing is lost or corrupted; the sequences just need to be moved past the highest existing key.

Does Data Pump migrate sequence values?

Data Pump exports a sequence as a CREATE SEQUENCE statement whose START WITH value is the sequence’s position at export time, specifically its LAST_NUMBER, which for a cached sequence is the top of the cache rather than the last value actually used. So a full export and import does carry the sequence position as of the moment of the export. What it cannot do is follow the source after that: if the source keeps generating values and you later refresh only table data (for example with CONTENT=DATA_ONLY), the target sequences stay at the old position.

How do I check whether sequences are behind after a migration?

For each sequence that feeds a key, compare the value it will hand out next with the maximum key in the table it feeds. DBA_SEQUENCES.LAST_NUMBER gives the next value that has not been cached yet, and SELECT MAX(column) gives the highest existing key; if LAST_NUMBER is less than or equal to the maximum, the sequence is behind and will produce a duplicate key. Oracle does not record which sequence feeds which column unless the column uses it as a DEFAULT or is an identity column, so keep the sequence-to-column mapping in the cutover runbook and run the check as a go/no-go step before go-live.

How do I reset an Oracle sequence to a specific value?

On Oracle 18c and later, use ALTER SEQUENCE owner.name RESTART START WITH n, where n is the next value you want handed out, typically the maximum existing key plus one. It resets the sequence in place and keeps its grants and dependents. On older releases, either temporarily set INCREMENT BY to the size of the gap, select NEXTVAL once, and set INCREMENT BY back to its original value, or drop and recreate the sequence with the right START WITH and re-grant it.

What about identity columns?

Identity columns are backed by an internal sequence and have the same problem if the table data is reloaded without moving that sequence. Oracle provides a direct fix: ALTER TABLE table_name MODIFY column_name GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) sets the identity’s next value past the current maximum value in the column. Use the GENERATED clause that matches how the column is defined (ALWAYS or BY DEFAULT). You can see identity definitions in USER_TAB_IDENTITY_COLS.

Why didn’t the problem show up in the migration rehearsal?

Two reasons are common. First, rehearsals validate data (row counts, checksums, reconciliation), and the data really is correct; nothing compares sequence positions to keys. Second, cached sequences are exported at the top of their cache, which can be well ahead of the last value actually used, especially on releases that grow sequence caches dynamically. A rehearsal that generates only a little test activity between the initial load and the refresh never uses up that headroom, while days of production traffic do.

Can I fix it by disabling the primary key constraint?

No. Disabling or dropping the constraint makes the error disappear by allowing duplicate keys, which silently corrupts the data model and breaks anything that relies on the key being unique. The constraint is doing its job by refusing duplicates. The correct fix is to move the sequence past the highest existing key with ALTER SEQUENCE … RESTART START WITH max plus one, and to make sequence resynchronization an explicit step in the cutover runbook.

Sequence drift is the third of the migration failures that sail through a “successful” cutover, alongside Data Pump jobs nobody counted and character-set conversions that quietly damage data. The pattern is the same each time: the tooling reports success, so verify the thing the tooling doesn’t check. Choose the method with the migration decision guide, then make sequence resync and its check explicit steps in the cutover runbook. And prove it the way that ends the argument: with the sequence-cutover lab, where every row arrives intact and the first new order still fails until the sequence is moved.

Originally published at https://uptimearchitect.com on October 4, 2026.

Nothing is broken here, and the delete did what it was told. The full scan simply doesn’t read rows — it reads blocks, every block up to a boundary called the high-water mark, and DELETE never moves that boundary. This post is what the high-water mark is, why a purge leaves it where it was, how to find tables carrying that dead weight, the fix, and a lab that measures the same scan before the delete, after it, and after the fix.

What the high-water mark is

A table is stored in a segment: a set of blocks the database allocates as the table grows. As rows are inserted, blocks get formatted and filled from the start of the segment outward. The high-water mark (HWM) is the boundary between blocks that have ever been used and blocks that never have.

A full table scan has no index telling it where the rows are, so it reads every block below the high-water mark and looks inside each one. Blocks above the HWM are skipped — they have never held data. Blocks below it are read whether they contain a thousand rows or none.

The optimizer works the same way: it costs a full scan from the table’s block count in its statistics. A table with a high HWM doesn’t just run like a big table, it looks like one to the optimizer too.

Why DELETE doesn’t lower it

DELETE removes rows from blocks. It does not hand the blocks back, and it does not move the high-water mark. The emptied blocks stay part of the segment, below the HWM, marked as having free space so that future inserts can reuse them. That is a sensible design for a table that will grow back - but for a table that was purged and won't, every full scan now reads thousands of blocks that are empty, or nearly so.

So the table’s cost to scan is set by its largest-ever size, not its current size. A purge that removes 99% of the rows removes 0% of the full-scan work.

A full table scan reads every block below the high-water mark, empty or not. Loading 200,000 rows pushes the HWM out to about 6,175 blocks. Deleting 99% of the rows empties those blocks but leaves the HWM where it was, so the same scan still reads about 6,000 blocks to find 2,000 rows. SHRINK SPACE packs the survivors into the start of the segment and lowers the HWM, and the scan drops to about 60 blocks.

Prove it

The lab loads a 200,000-row table (about 200 bytes a row) and measures one full scan — SELECT /*+ FULL(t) */ COUNT(pad) FROM hwm.t - in consistent gets, the logical block reads the scan performs, taken from V$MYSTAT. Then it deletes 99% of the rows, commits, and runs the same scan:

                       rows   full-scan gets   blocks below HWM
loaded              200,000            6,074              6,175
after DELETE 99%      2,000            6,074              6,175
after SHRINK SPACE    2,000               64                 62

One percent of the rows, one hundred percent of the work — until the high-water mark comes down. After the shrink, the scan’s cost follows the data: 64 gets for 2,000 rows.

Don’t take my word for it — run it. The high-water mark lab loads the table on Oracle Database Free, measures the full scan, deletes 99% of the rows and asserts the scan still costs at least 90% of the baseline, then runs SHRINK SPACE and asserts it drops to 5% or less with every remaining row intact. If the bloat doesn't reproduce, or the shrink doesn't fix it, the run fails. Proven on every CI push.

How to find bloated tables

You don’t need to guess. Compare what a table occupies with what its rows actually need, using fresh optimizer statistics in DBA_TABLES:

SELECT owner, table_name, num_rows, blocks,
       CEIL(num_rows * avg_row_len / (8192 * 0.9))                         AS blocks_needed,
       ROUND(100 * (1 - (num_rows * avg_row_len / (8192 * 0.9))
                        / NULLIF(blocks, 0)))                              AS pct_wasted
FROM   dba_tables
WHERE  owner = 'APP'
AND    blocks > 1000
ORDER  BY blocks - CEIL(num_rows * avg_row_len / (8192 * 0.9)) DESC;

blocks is the space below the high-water mark; blocks_needed estimates the space the current rows would fill at about 90% density (adjust 8192 if your block size differs). In the lab, after the delete, this reports 6,175 blocks for rows that need about 58 - 99% wasted. Two caveats: it is only as current as the statistics, so gather first; and it is an estimate, so treat it as a ranking of where to look, not a precise measurement. For precise per-block fullness, DBMS_SPACE.SPACE_USAGE reports how many blocks are empty, 0-25% full, and so on; the database's automatic Segment Advisor also flags segments with reclaimable space.

Prioritise tables that are both bloated and full-scanned: check AWR or V$SQL_PLAN for TABLE ACCESS FULL on them. A bloated table that is only ever read by primary key costs you disk space, not query time.

The fix

SHRINK SPACE compacts the rows into the start of the segment, lowers the high-water mark and releases the freed space:

ALTER TABLE app.audit_log ENABLE ROW MOVEMENT;
ALTER TABLE app.audit_log SHRINK SPACE;

• It needs row movement, because rows are physically relocated and get new ROWIDs.
• It works on tables in ASSM (automatic segment space management) tablespaces, which is the default.
• It is online: DML continues during the compaction, with only a brief lock at the end to move the HWM.
• Indexes stay usable and are maintained as rows move. Add CASCADE to shrink dependent indexes too.
• SHRINK SPACE COMPACT does the row movement but stops short of moving the HWM, so you can split the work: compact during the day, then run a quickSHRINK SPACE in a quiet window.

ALTER TABLE ... MOVE ONLINE (12.2 and later) is the alternative: it rebuilds the table into a new segment, keeps indexes usable, and allows DML throughout, at the cost of needing space for a second copy while it runs. A plain MOVE without ONLINE blocks DML and leaves every index UNUSABLE until rebuilt - a common way to turn a cleanup into an outage. A handful of segment types can't be shrunk; MOVE ONLINE is the usual fallback.

After either one, gather statistics, so the optimizer sees the new, smaller block count.

What teams get wrong

• Expecting DELETE to make things faster. It reduces rows, not blocks. Full scans, and the optimizer's costing of them, keep paying for the table's largest-ever size until the HWM is lowered.
• Running the purge as one giant DELETE every time. For data that ages out by date, partition by date and drop or truncate old partitions instead: it releases the space immediately, generates almost no undo, and lets queries skip old data entirely withpartition pruning.
• Using MOVE without ONLINE. Every index on the table goes UNUSABLE, and queries relying on them fail or fall back to full scans until someone rebuilds them.
• Shrinking tables that will grow straight back. Conventional inserts reuse the free space below the HWM. If a table is purged and then refilled on a cycle, shrinking it just makes the next cycle re-allocate the same space. Shrink tables that were purged and will stay small.
• Forgetting direct-path loads. INSERT /*+ APPEND */ always writes above the high-water mark and never reuses free space below it. A table loaded by direct path and purged byDELETE grows forever.
• Skipping the statistics afterwards. Until stats are refreshed, the optimizer still believes the table is thousands of blocks and costs plans accordingly.

Frequently asked questions

What is the high-water mark in Oracle?

The high-water mark (HWM) is the boundary in a table’s segment between blocks that have been used to store data at some point and blocks that never have. As rows are inserted, the database formats and fills blocks from the start of the segment outward, pushing the high-water mark up. A full table scan reads every block below the high-water mark, whether those blocks currently contain rows or are empty, and skips the blocks above it. The optimizer also uses the table’s block count from statistics, which reflects the space below the high-water mark, to cost full scans.

Why doesn’t DELETE make a full table scan faster?

Because DELETE removes rows from blocks but does not release the blocks or lower the high-water mark. The emptied blocks remain part of the table’s segment, below the high-water mark, marked as having free space for future inserts. A full table scan reads every block below the high-water mark, so after deleting 99% of the rows it still reads almost exactly the same number of blocks. In the companion lab the same full scan costs 6,074 consistent gets with 200,000 rows and still 6,074 after deleting all but 2,000 of them. Only lowering the high-water mark, with SHRINK SPACE, MOVE, or TRUNCATE, reduces the scan’s work.

How do I lower the high-water mark of an Oracle table?

The usual online method is ALTER TABLE … ENABLE ROW MOVEMENT followed by ALTER TABLE … SHRINK SPACE, which compacts the rows toward the start of the segment, lowers the high-water mark, and releases the freed space while allowing concurrent DML. It requires an ASSM tablespace, which is the default. Alternatively, ALTER TABLE … MOVE ONLINE (12.2 and later) rebuilds the table into a new segment while keeping indexes usable. TRUNCATE also resets the high-water mark instantly but removes every row. After any of these, gather optimizer statistics so plans reflect the smaller table.

What is the difference between SHRINK SPACE and SHRINK SPACE COMPACT?

SHRINK SPACE does two phases in one command: it moves rows toward the beginning of the segment, then lowers the high-water mark and releases the space, taking a brief lock at the end to do so. SHRINK SPACE COMPACT performs only the first phase, the row movement, and leaves the high-water mark where it is. That lets you split the work: run the compaction, which can take a while on a large table, during normal hours, and later run a plain SHRINK SPACE in a quiet window, which then completes quickly because the rows are already packed. Adding CASCADE also shrinks the table’s dependent indexes.

Does SHRINK SPACE lock the table or invalidate indexes?

SHRINK SPACE is an online operation: queries and DML can continue while rows are being compacted, and only a short exclusive lock is taken at the end to adjust the high-water mark. Indexes remain usable because they are maintained as rows move to new locations. Row movement must be enabled first because rows receive new ROWIDs, so anything that stores ROWIDs long-term would need to tolerate that. This differs from a plain ALTER TABLE … MOVE without the ONLINE keyword, which blocks DML and marks every index on the table UNUSABLE until it is rebuilt.

How can I find tables with a lot of wasted space below the high-water mark?

After gathering fresh statistics, compare each table’s BLOCKS in DBA_TABLES, which is the space below the high-water mark, with the space its rows need, estimated as NUM_ROWS times AVG_ROW_LEN divided by the usable bytes per block (for example 8192 times 0.9). Tables where BLOCKS is far larger than that estimate are carrying dead space; in the companion lab a purged table shows 6,175 blocks against about 58 needed. DBMS_SPACE.SPACE_USAGE gives an exact breakdown of how full the blocks are, and the automatic Segment Advisor reports segments with reclaimable space. Focus on bloated tables that are also read by full scans, since those are where the space turns into query time.

Should I shrink every table after deleting rows?

No. Conventional inserts reuse the free space below the high-water mark, so a table that is regularly purged and then refilled will simply grow back into the same space, and shrinking it only adds work. Shrinking pays off for tables that were purged and will stay much smaller, and that are read by full scans. For data that ages out by date on a schedule, partitioning by date and dropping or truncating old partitions is usually better than repeated DELETE plus SHRINK: it releases space immediately, generates little undo and redo, and lets queries skip old partitions entirely.

High-water mark bloat is the same lesson the rest of the performance series keeps arriving at from different directions: the cost of a query is the work the engine actually does, not the size of the answer. Reading the execution plan shows you a TABLE ACCESS FULL; the right index avoids reading the whole table at all; partition pruning skips the parts you don't need; and fresh statistics make sure the optimizer is costing the table it actually has. When a purge didn't make anything faster, check the high-water mark: find the tables whose blocks dwarf their rows, shrink the ones that are full-scanned and won't grow back, and prove it the way that ends the argument - with the high-water mark lab, where deleting 99% of a table changes nothing until the shrink drops the same scan from 6,074 gets to 64.

Originally published at https://uptimearchitect.com on October 3, 2026.

You Deleted 99% of the Rows. The Full Scan Didn’t Get Any Faster. was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.

The database is patched. The data files are encrypted with TDE. The audit trail is clean. And every query your application runs — along with every row it gets back — crosses the network as readable text. Not because anyone turned encryption off, but because nobody ever turned it on: out of the box, an Oracle client and an Oracle server agree to talk in plaintext.

That is not a theoretical weakness. Anyone who can see the packets — a compromised jump host, a mirrored switch port, a misconfigured cloud network, a curious admin with tcpdump - can read the SQL, the bind values, and the result sets. This post shows exactly what is on the wire, why the default is plaintext, the four lines of sqlnet.ora that fix it, how to verify a session is actually encrypted, and a lab that captures the same session before and after.

What is actually on the wire

Here is what a packet capture of an ordinary session looks like on a default install. The APP user connects over TCP and runs one query against a table holding a confidential value; tcpdump -A on port 1521 shows:

wire> select 'ROWS='||count(*) from vault where secret like 'CANARY-%' wire> CANARY-7731-CONFIDENTIAL

That is the SQL text and the result value, lifted straight out of the packets. No exploit, no credentials, no access to the database — just a view of the network path.

Two things are worth being precise about. First, the password is not the problem: Oracle’s logon is a challenge-response, so the password itself does not cross in the clear even here. Everything after the logon does. Second, this is not an “old version” issue. The capture above is from the current Oracle Database Free release, with nothing misconfigured — it is simply the default.

Why the default is plaintext

Oracle’s native network encryption is negotiated per connection, and each side has a setting in sqlnet.ora: SQLNET.ENCRYPTION_SERVER on the database host and SQLNET.ENCRYPTION_CLIENT on the client. Each can be one of four values:

• REJECTED - never encrypt; refuse a peer that insists.
• ACCEPTED - encrypt only if the other side asks. This is the default on both sides.
• REQUESTED - ask for encryption, but fall back to plaintext if the other side won't.
• REQUIRED - encrypt or refuse the connection.

The trap is in that default. When both ends are ACCEPTED, each is willing to encrypt but neither asks - so the negotiation succeeds with no encryption at all. Nothing is broken, nothing logs a warning, and the session runs in plaintext forever.

The integrity side works the same way with SQLNET.CRYPTO_CHECKSUM_SERVER / _CLIENT: a per-packet cryptographic checksum that detects tampering and replay in transit. It is a separate switch, and it defaults to off for the same reason.

Native network encryption is negotiated per connection from the two sides’ settings. With the default ACCEPTED on both ends, each side is willing but neither asks, so the session runs in plaintext. Setting the server to REQUIRED forces every new session to negotiate AES256 (clients default to ACCEPTED, so no client change is needed). A client that is explicitly REJECTED is refused with ORA-12660 rather than silently falling back to plaintext.

The fix: four lines on the server

Make encryption and integrity required on the database server, and pin strong algorithms. In the server’s sqlnet.ora (normally $ORACLE_HOME/network/admin, or wherever TNS_ADMIN points):

SQLNET.ENCRYPTION_SERVER = REQUIRED SQLNET.ENCRYPTION_TYPES_SERVER = (AES256) SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256)

Why the server, and why REQUIRED:

• One change covers every client. Clients default to ACCEPTED, so once the server requires encryption, every client that hasn't explicitly rejected it simply negotiates it. No application change, no client redeploy.
• REQUIRED, not REQUESTED. REQUESTED falls back to plaintext if a peer declines - which means a misconfigured client silently stays readable.REQUIRED makes that client fail loudly with ORA-12660 instead, which is what you want to find in testing, not in an incident.
• Pin the algorithm list. List only the algorithms you accept (AES256, and AES192/AES128 if you must), so nothing negotiates down to a legacy cipher. Same for the checksum: SHA-2 family only.
• Integrity is not optional. Encryption without the checksum hides the data but doesn’t stop tampering or replay. Require both.

No restart is needed: the server reads sqlnet.ora when it starts a new server process for a new connection. That also means existing sessions keep whatever they negotiated - recycle connection pools after the change, or they will stay in plaintext until they reconnect.

Verify it, don’t assume it

A config file is a claim. The proof is what each session actually negotiated, which Oracle exposes in V$SESSION_CONNECT_INFO:

SELECT network_service_banner
FROM   v$session_connect_info
WHERE  sid = SYS_CONTEXT('USERENV', 'SID');

On an encrypted session you will see lines naming the algorithm in use — AES256 Encryption service adapter... and SHA256 Crypto-checksumming service adapter.... On a plaintext session you will only see the generic "Encryption service for Linux" / "Crypto-checksumming service" lines with no algorithm adapter. Run the same check across all sessions (drop the WHERE, join to V$SESSION for usernames and programs) to find any connection still in the clear.

Don’t take my word for it — run it. The SQL*Net encryption lab stands up Oracle Database Free and a throwaway tcpdump sidecar that shares the database's network namespace. The APP user reads a confidential row over TCP: on the default config the session negotiates no encryption and the capture contains both the SQL text and the secret value. It then adds the four sqlnet.ora lines, runs the same session under the same capture, and asserts the session negotiated AES256 / SHA256 and that neither the SQL nor the secret appears anywhere in the packets. If the plaintext doesn't reproduce, or the fix doesn't hide it, the run fails. Proven on every CI push.

In the lab, the before-and-after is unambiguous:

default : session encryption: NONE integrity: NONE SQL seen 2x, secret seen 1x fixed : session encryption: AES256 integrity: SHA256 SQL seen 0x, secret seen 0x

Native encryption or TLS?

Oracle offers two ways to encrypt the connection, and both are included in every edition at no extra cost:

• Native network encryption (what this post configures) — a few lines of sqlnet.ora, no certificates, and it works with existing connect strings on port 1521. The fastest way to get every session off plaintext.
• TLS (TCPS) — certificate-based, usually on a separate port. Its key advantage is server authentication: the client verifies it is talking to the real database, which protects against a rogue or spoofed listener in the middle. Native encryption has no such check. TLS is also what many compliance frameworks name explicitly, and it encrypts the connect packet that native encryption leaves readable.

A practical path: turn on native encryption with REQUIRED now - it closes the plaintext gap today with almost no risk - and plan TLS where you need server authentication or an auditor asks for it by name.

What teams get wrong

• Assuming TDE covers it. TDE encrypts data at rest — data files and backups. The moment a row is read and sent to a client, TDE is out of the picture. In-transit protection is a separate control.
• Assuming the network is private. “It’s all inside the VPC / the data center” is exactly the assumption an attacker with one foothold relies on. Internal traffic is where lateral movement happens.
• Setting REQUESTED and calling it done. It looks like encryption is on, but any peer that declines gets a plaintext session with no error. UseREQUIRED and let incompatible clients fail where you can see them.
• Encrypting without integrity. Without CRYPTO_CHECKSUM, traffic is hidden but can still be modified or replayed in transit. Require both.
• Never verifying. A sqlnet.ora in the wrong directory, an overriding TNS_ADMIN, or a pool that never reconnected all leave sessions in plaintext while the config looks correct. CheckV$SESSION_CONNECT_INFO.
• Expecting everything to be hidden. The initial connect packet — service name, client program, OS user — is sent before negotiation and stays readable with native encryption. The database username, SQL and data do not. If the connect descriptor itself is sensitive, that’s an argument for TLS.

Is Oracle database network traffic encrypted by default?

No. By default both the Oracle client and the database server have their native network encryption setting at ACCEPTED, which means each side is willing to encrypt but neither one requests it, so the connection is negotiated without encryption. SQL statements, bind values and result sets then cross the network as readable bytes that anyone who can capture the packets can read. The logon password is protected by Oracle’s challenge-response authentication, but everything after logon is in plaintext until you configure encryption, either with native network encryption in sqlnet.ora or with TLS (TCPS).

How do I enable Oracle native network encryption?

Add four lines to the database server’s sqlnet.ora (normally in $ORACLE_HOME/network/admin or the directory TNS_ADMIN points to): SQLNET.ENCRYPTION_SERVER = REQUIRED, SQLNET.ENCRYPTION_TYPES_SERVER = (AES256), SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED, and SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA256). Because clients default to ACCEPTED, every new connection then negotiates AES256 encryption and SHA-256 integrity without any client change. No database restart is needed, but existing sessions keep what they already negotiated, so recycle application connection pools afterwards and verify the result in V$SESSION_CONNECT_INFO.

What is the difference between ACCEPTED, REQUESTED and REQUIRED?

They control how each side negotiates encryption. REJECTED never encrypts and refuses a peer that requires it. ACCEPTED, the default, encrypts only if the other side asks. REQUESTED asks for encryption but falls back to plaintext if the other side declines. REQUIRED insists on encryption and refuses the connection otherwise. Encryption is used when at least one side requests or requires it and the other does not reject it; if one side is REQUIRED and the other REJECTED, the connection fails with ORA-12660. Using REQUIRED on the server is the safe choice because it turns a silent plaintext fallback into a visible error.

How can I check whether an Oracle session is encrypted?

Query V$SESSION_CONNECT_INFO for the session, for example SELECT network_service_banner FROM v$session_connect_info WHERE sid = SYS_CONTEXT(‘USERENV’,’SID’). An encrypted session shows a line naming the algorithm adapter in use, such as ‘AES256 Encryption service adapter’, and an integrity-protected session shows a line such as ‘SHA256 Crypto-checksumming service adapter’. A plaintext session only shows the generic encryption and crypto-checksumming service lines with no algorithm adapter. To audit the whole instance, query it for all sessions joined to V$SESSION to see which users and programs are still connecting without encryption.

Does native network encryption need a separate license?

No. Oracle native network encryption and data integrity, as well as TLS (TCPS) for database connections, were once part of the separately licensed Advanced Security option, but Oracle made network encryption available in all editions of the database years ago. Transparent Data Encryption and Data Redaction remain part of Advanced Security, but encrypting traffic between clients and the database does not require it.

Should I use native network encryption or TLS?

Both encrypt the traffic. Native network encryption is configured with a few sqlnet.ora parameters, needs no certificates, and works with existing connect strings, which makes it the fastest way to get every session off plaintext. TLS (TCPS) uses certificates and adds server authentication, so the client verifies it is connected to the genuine database and is protected against a spoofed listener or man-in-the-middle; it also encrypts the initial connect packet and is the control many compliance frameworks name explicitly. A common approach is to enable native encryption with REQUIRED immediately, and adopt TLS where server authentication or a specific compliance requirement calls for it.

Does Transparent Data Encryption protect data in transit?

No. Transparent Data Encryption (TDE) encrypts data at rest, in data files, redo, and backups, so that stolen files or media are unreadable. When a session queries the data, the database decrypts it and sends the result to the client, and at that point TDE no longer applies. Protecting data as it travels between the client and the database requires network encryption, either native network encryption or TLS. A complete setup uses both: TDE for data at rest and network encryption for data in transit.

Network encryption is the in-transit half of a picture the rest of the security series builds from other angles: the hardening checklist decides who can connect and how, TDE protects the data files and backups at rest, redaction narrows which values reach a screen, and unified auditing records who asked. None of them help once a result set is crossing the network in readable bytes. Set the server to REQUIRED, pin AES256 and SHA-256, verify every session in V$SESSION_CONNECT_INFO, and then prove it the way that ends the argument: with the SQL*Net encryption lab, where the same query is readable in a packet capture before the fix and unreadable after.

Originally published at https://uptimearchitect.com on September 30, 2026.

Your Oracle Traffic Is Readable on the Wire. Here’s the Proof, and the Fix. was originally published in System Weakness on Medium, where people are continuing the conversation by highlighting and responding to this story.

The cutover ran clean. impdp reported completed successfully, the row counts matched to the row, the application came up, and everyone went home. Three weeks later a support ticket lands: a customer named José is in the system as Jose, and a partner in Tokyo shows up as ??. Not a rendering bug - the database really holds Jose and ?? now. The originals are gone, and no backup from after the cutover has them either.

Here’s the uncomfortable part: nothing failed. No error was raised, no row was rejected, no count was off. The migration did exactly what you told it to — you just told it to pour Unicode data into a container too narrow to hold it, and Oracle quietly trimmed everything that didn’t fit. This post is what character-set data loss actually is, why every check you ran said “success,” the one test that catches it, and a lab that reproduces the loss on demand and then shows the migration that keeps every character.

What actually happens

A character set is the map between the characters you see and the bytes stored on disk. Oracle has two that matter here: the database character set ( NLS_CHARACTERSET), which governs VARCHAR2/ CHAR data, and the client's declared character set ( NLS_LANG), which tells the server what the bytes arriving from a client mean. Modern databases use AL32UTF8 - Unicode - which can represent every character in every language. Many older databases used a narrow set: US7ASCII (7-bit, 128 characters), WE8MSWIN1252 (Western European), WE8ISO8859P1, and so on.

When data moves from a wider character set into a narrower one — an export/import into a legacy target, a CREATE TABLE AS across a database link, a client conversion - Oracle must map each character into the target set. For a character the target can represent, fine. For one it can't, it does one of two things, and both are data loss:

• Silent transliteration. A character with a rough ASCII equivalent is replaced by that equivalent. José becomesJose, Müller becomes Muller, François becomes Francois. It looks completely plausible - which is exactly why it sails through QA and lands in production.
• Replacement. A character with no mapping at all becomes a replacement character, almost always ?. A CJK name becomes??; an emoji or an unusual symbol becomes ?. Obvious once someone looks - but nobody looks at every row.

Crucially, this conversion is a normal, “successful” operation. It does not raise an error. It does not reject the row. The row count is identical before and after. Every signal a migration runbook checks says the load worked.

Why every check said “success”

The standard post-migration checks all operate at the wrong level:

• Row counts compare how many rows moved, not what’s in them. José and Jose are both one row.
• “0 errors” / completed successfully means no row was rejected. Character-set conversion isn't a rejection - it's a transformation the database is happy to perform.
• Spot-checking finds the ?? names if you happen to look at one, but misses the transliterated ones entirely, becauseJose looks correct.

The loss is in the values, so you have to compare the values — specifically, whether each value would survive a round-trip through the target character set. That’s the check almost nobody runs, and it’s the whole game.

The same Unicode (AL32UTF8) rows loaded into three target character sets. Every path reports success and keeps all the rows — the difference is only in the values. A US7ASCII target strips Latin accents to plain ASCII (Jose) and turns anything with no mapping into ‘?’; a WE8MSWIN1252 (Western European) target keeps the accents but still loses the CJK name; only a Unicode (AL32UTF8) target, a superset, preserves every character. Row counts match and no error is raised in all three cases.

Reproduce it

You don’t need a real migration to see this. Build a 10-row customers table in an AL32UTF8 database - five plain-ASCII names and five with accents or CJK characters - then model loading it into a narrower target with CONVERT(name, target_charset, 'AL32UTF8'), which does exactly what the target database's conversion does on import. Migrating into US7ASCII:

rows after migration: 10 errors raised: 0 names damaged by a US7ASCII target: 5 of 10 (of those, turned into '?': 1) names still damaged by a WE8MSWIN1252 (Western European) target: 1 José -> Jose (US7ASCII target) Müller -> Muller (US7ASCII target) Renée -> Renee (US7ASCII target) François -> Francois (US7ASCII target) 田中 -> ?? (US7ASCII target)

Ten rows in, ten rows out, zero errors — and half the names quietly damaged. Four were transliterated into plausible-but-wrong ASCII; one became ??. Note the middle row of the story: even a Western European target ( WE8MSWIN1252), which happily keeps José and Müller, still turns the CJK name into ??. "Wide enough for our data" is a judgment that ages badly the moment you onboard a customer from a new region.

Detect it — before cutover

The test that catches what row counts miss is a round-trip: convert each value to the target character set and back, and compare it to the original. Anything that comes back changed would be damaged by the migration:

-- pre-migration scan: which rows would a US7ASCII target silently damage?
select id, name
from   customers
where  convert(convert(name,'US7ASCII','AL32UTF8'),'AL32UTF8','US7ASCII') <> name;

In the lab this flags exactly the five non-ASCII rows, in advance. This is precisely what Oracle’s Database Migration Assistant for Unicode (DMU) — and the legacy csscan utility — do for real: scan every column of every table and report the data that is lossy (can’t be represented in the target) or would need byte expansion. Run it against the source before you migrate, not against the wreckage afterward.

The fix

• Migrate into a Unicode target (AL32UTF8). Unicode is a superset of every legacy Oracle character set, so every source character has a representation and nothing is lost. In the lab, the same data migrated into AL32UTF8 has a round-trip loss ofzero. If you’re modernizing anyway, this is the answer: go to AL32UTF8 and stop having this problem forever.
• If you must land in a narrower set, scan first and remediate. Use DMU/csscan to find lossy data, then fix it at the source (correct the values, or widen the target) before cutover - never discover it after.
• Set the client NLS_LANG correctly on every tool in the pipeline. More on this trap below; a wrongNLS_LANG corrupts data even when both databases are Unicode.
• Watch column length semantics when migrating up to Unicode. Going from a single-byte set to AL32UTF8, characters that were one byte can become up to four. AVARCHAR2(10 BYTE) column that held a 10-character name may no longer fit - the import fails withORA-12899, or worse, silently truncates. Define columns withCHAR semantics (VARCHAR2(10 CHAR)) or size for the byte expansion.

What teams get wrong

• Trusting the row count and the “completed successfully”. Both are true and both are irrelevant to character-set loss. The count tells you rows moved; it tells you nothing about whether the values survived.
• Thinking ? is the only symptom. The ?-marks are the lucky case - they're visible. The silent transliteration (José→Jose) is the dangerous one: it looks right, passes review, and you find out when a customer complains that you've been spelling their name wrong for a month.
• The NLS_LANG pass-through trap. The other classic corruption isn't a narrow database - it's a wrongclient. If a tool inserts UTF-8 bytes while its NLS_LANG claims US7ASCII, Oracle believes the lie, does no conversion (or the wrong one), and stores garbage - even when the database is AL32UTF8. "Both sides are Unicode" does not save you if a client in the middle misdeclares its character set. SetNLS_LANG on every client, exporter, and loader.
• Assuming “Western European is wide enough”. It holds accented Latin, so it survives a demo full of French and German names — and destroys the first Japanese, Greek, Arabic, or emoji-bearing value it meets.
• Migrating first, scanning never. DMU/csscan exist to be run before the migration. Skipping the scan is choosing to find your data loss in production.
• No backup of the pre-migration source. Character-set loss is irreversible: once José is stored asJose, the accent is gone. If you didn't scan and you didn't keep the source, there is nothing to recover from. Keep the source readable until you've verified the target at the value level.

What causes Oracle character-set data loss during a migration?

It happens when data moves from a wider character set into one that cannot represent all of its characters — most commonly Unicode (AL32UTF8) data being loaded into a narrow legacy set like US7ASCII or WE8MSWIN1252. Oracle must map each character into the target set, and for characters the target cannot hold it either transliterates them to a rough ASCII equivalent (José becomes Jose, the accent silently dropped) or replaces them with a replacement character, usually ‘?’ (a CJK name becomes ??). This conversion is treated as a normal, successful operation: no error is raised, no row is rejected, and the row count is unchanged, so it passes every count-based migration check while quietly destroying data. A separate but related cause is a mismatched client NLS_LANG, where a tool misdeclares the character set of the bytes it is sending and Oracle stores them incorrectly even when both databases are Unicode.

Why did my migration report success but the data is corrupted?

Because character-set conversion is not an error condition. The checks a migration runbook relies on — ‘completed successfully’, zero rejected rows, and matching source and target row counts — all operate on rows and errors, not on the contents of the values. When Oracle converts a character it cannot represent in the target set, it transforms the value (to a transliteration or a ‘?’) rather than failing, so every row still loads, no error is logged, and the counts match exactly. The loss lives inside the values, which none of those checks inspect. To see it you have to compare the actual values, typically with a round-trip conversion test, rather than trusting the success message.

How do I check for character-set data loss before migrating?

Run a round-trip test: convert each value to the target character set and back to the source set, and compare it to the original — any value that comes back changed cannot survive the target set. In SQL that is a query like WHERE CONVERT(CONVERT(col,’US7ASCII’,’AL32UTF8'),’AL32UTF8',’US7ASCII’) <> col, run against every character column. For a real migration, use Oracle’s Database Migration Assistant for Unicode (DMU) or the legacy csscan utility, which scan every column of every table and report which data is lossy (cannot be represented) or would need byte expansion. The essential discipline is to run this against the source before cutover, not to discover the damage in production afterward.

What is the difference between transliteration and ‘?’ replacement?

They are the two ways Oracle handles a character the target set cannot represent, and both are data loss. Transliteration substitutes a rough equivalent that the target set does have — José becomes Jose, Müller becomes Muller — so the result is readable and plausible but wrong; this is the more dangerous case because it passes visual review and QA, and you may not notice for weeks. Replacement is used when there is no equivalent at all: the character becomes a replacement character, almost always ‘?’, so a CJK or Arabic name becomes a string of question marks. Replacement is obvious once someone looks at the row; transliteration is silent. Both are irreversible once the original value has been overwritten.

Does migrating to AL32UTF8 (Unicode) cause data loss?

Migrating into AL32UTF8 does not lose characters, because Unicode is a superset of every legacy Oracle character set — every source character has a representation, so the conversion is lossless. That direction is the fix, not the risk. There are two things to watch, though. First, column length semantics: going from a single-byte set to AL32UTF8, a character that was one byte can become up to four, so a VARCHAR2(n BYTE) column may no longer fit its data and the load fails with ORA-12899 or truncates — define columns with CHAR semantics or size for the expansion. Second, a wrong client NLS_LANG can still corrupt data during the load even with a Unicode target, if a tool misdeclares the character set of the bytes it sends. The data loss described here is specifically migrating the other way, into a narrower set.

What is the NLS_LANG pass-through trap?

It is character corruption caused not by a narrow database but by a client that misdeclares its character set. NLS_LANG tells the Oracle server what the bytes arriving from a client mean. If a tool actually sends UTF-8 bytes but its NLS_LANG claims the data is US7ASCII (or WE8MSWIN1252), the server believes the declaration and either performs the wrong conversion or none at all, storing mangled bytes — and this happens even when both the source and target databases are AL32UTF8. It is a common surprise because teams assume that if both databases are Unicode the data is safe, but a single exporter, loader, or client session in the pipeline with a wrong NLS_LANG will corrupt the data in transit. The remedy is to set NLS_LANG to the true character set of the data on every client and tool involved in the migration.

Why did my import fail with ORA-12899 after moving to Unicode?

ORA-12899 (value too large for column) on a migration into AL32UTF8 is the byte-expansion trap. In a single-byte character set every character occupies one byte, so a VARCHAR2(10) column holds up to 10 characters. In AL32UTF8 a character can take up to four bytes, and if the column was defined with BYTE length semantics (the default), VARCHAR2(10 BYTE) still means 10 bytes — so a 10-character name of accented or multibyte characters no longer fits and the insert fails. The fixes are to define the columns with CHAR semantics (VARCHAR2(10 CHAR), which counts characters regardless of byte length), to set NLS_LENGTH_SEMANTICS to CHAR before creating the tables, or to widen the columns to allow for the expansion. It is better to hit ORA-12899 (a loud failure) than silent truncation, so size deliberately rather than relying on defaults.

Can I recover data after a character-set migration lost it?

Not from the migrated database — the loss is irreversible. Once José has been stored as Jose, the accent is simply gone; once a name has been stored as ??, the original characters are not recoverable from that value, because the conversion discarded information rather than encoding it differently. Your only recovery is the pre-migration source: the original database, an export taken before cutover, or a backup from before the load. This is exactly why the source must be kept readable and verified at the value level before it is decommissioned, and why the round-trip scan (or DMU/csscan) must be run before the migration. If the source is already gone and was already overwritten, the data cannot be reconstructed.

Character-set loss belongs to the same family of migrations that “succeed” without being correct: it is the values-level cousin of counting the rows after a Data Pump job, where a load reports success but the data isn’t whole — here the rows are all present and it’s the characters inside them that are missing. It’s the detail that turns a clean-looking cloud migration into a month-late incident. Treat “completed successfully” as the start of verification, not the end of it: scan the source for lossy data with DMU or a round-trip query, migrate into a Unicode (AL32UTF8) target so there is nothing to lose, set NLS_LANG correctly on every client, and prove it the way that ends the argument - with the character-set migration lab, where a US7ASCII target silently damages half the names and a Unicode target keeps every one.

Originally published at https://uptimearchitect.com on September 26, 2026.

Here’s the part that turns the panic into a ten-minute fix: ORA-00257 is not a failure — it’s a refusal. The database is in ARCHIVELOG mode, which means it promises to archive every online redo log before reusing it, so you never lose a committed transaction. The archiver can’t keep that promise right now, so rather than overwrite redo it hasn’t safely copied, it stops accepting new redo altogether. Nine times out of ten the reason is mundane: the place the archived logs go is full. This post is what ORA-00257 actually means, how to confirm the cause in three queries, the fix that clears it without throwing away your recovery window, and a lab that forces the error on demand and then resolves it.

What ORA-00257 actually is

In ARCHIVELOG mode, Oracle writes changes to the online redo logs in a circle — a small, fixed set of groups it fills and reuses. Before it can reuse a group, the archiver process (ARCn) must copy that group to an archive destination — almost always the Fast Recovery Area (FRA), the managed disk area Oracle also uses for backups and flashback logs. That archived copy is what lets RMAN recover the database to any point in time.

ORA-00257 happens when the archiver cannot write the next archived log, so the online logs can’t be recycled, so the database runs out of redo space — and refuses new redo rather than lose it. The message says “connect internal only” (on current releases, Connect AS SYSDBA only until resolved): privileged sessions can still get in to fix it, but everyone else is locked out the instant they try to generate redo.

The critical reframing: ORA-00257 is the symptom the user sees; it is almost never the actual error. The real error is on the archiver side, and it’s usually ORA-19809: limit exceeded for recovery files - the FRA is full. Chase the ORA-00257 and you'll go in circles; chase the ORA-19809 and you'll be done in minutes.

Why it happens

The most common cause is the least dramatic one: archived logs piled up and nobody removed them. The FRA has a fixed size ( db_recovery_file_dest_size). Every log switch adds an archived log to it. If your backups aren't also deleting the archived logs they've captured - or you have no archive-log backup at all - the FRA fills at exactly the rate the database generates redo. A busy day, a bulk load, an index rebuild, and the area that was "80% full, we'll deal with it later" tips over.

Then the chain reaction:

• The FRA hits its size limit. ARCn tries to create the next archived log, fails with ORA-19809, and marks the archive destinationERROR. The alert log says, in as many words, Stuck archiver condition declared.
• The online redo logs keep filling with new changes, but none of them can be archived, so none can be reused.
• The moment the database needs to advance into a log it can’t recycle, it stops. Sessions generating redo block; new non-SYSDBA sessions are turned away with ORA-00257.

Other flavors exist — the archive destination points at a filesystem that filled, a directory that lost its permissions, or a Data Guard standby that’s unreachable — but they all reduce to the same thing: the archiver has nowhere to put the next log. The FRA-full case is by far the most common, and the one this lab reproduces.

Confirm the cause in three queries

Don’t guess — the state is fully visible. As SYSDBA:

-- 1. How full is the FRA, and is it the problem?
select space_limit/1024/1024 as limit_mb,
       space_used /1024/1024 as used_mb,
       round(space_used/space_limit*100) as pct
from   v$recovery_file_dest;
-- 2. What is filling it? (almost always ARCHIVED LOG)
select file_type, percent_space_used
from   v$recovery_area_usage
order  by percent_space_used desc;
-- 3. Is the archive destination in error, and why?
select dest_id, status, error
from   v$archive_dest
where  destination is not null;

If query 1 shows ~100% used, query 2 shows ARCHIVED LOG dominating it, and query 3 shows STATUS = ERROR with ORA-19809, you have the FRA-full case - and the alert log will confirm it with Stuck archiver condition declared. That's your whole diagnosis.

Reproduce it

You don’t have to wait for a 3 a.m. page to see this. Put a database in ARCHIVELOG mode, point archiving at a deliberately tiny 50 MB Fast Recovery Area, and generate a little redo. A couple of log switches fill the FRA; the archiver’s destination fails; then one “application” session floods redo and blocks on a log switch it can’t complete — driving the online logs to full-and-unarchived. Now a normal user tries to connect and work:

round 1: FRA=78% dest_1=ERROR >> A normal (non-SYSDBA) user now tries to connect and write... ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.

That’s the outage, reproduced deterministically: the archive destination is in ERROR (ORA-19809 under the hood), and every non-SYSDBA session is refused with ORA-00257 — while / as sysdba still connects, exactly as the message promises.

The fix

Two levers, in order. First stop the bleeding, then fix the cause.

Immediate relief — give the archiver room. If you have disk headroom, growing the recovery area unsticks the database in one command, because the archiver retries automatically the moment space appears:

alter system set db_recovery_file_dest_size = 500m scope=both;   -- size to your reality

The real fix — back the archived logs up and remove them. Growing the FRA only delays the next fill; the archived logs are still piling up. Reclaim the space and keep your recovery window by backing the logs up to a location outside the FRA and deleting the inputs, with RMAN:

RMAN> backup archivelog all format '/backup/arch_%U' delete all input;

That copies every archived log to /backup, deletes the FRA copies it just secured, and frees the space - so you can still recover, and the FRA drains. Once there's room, the destination returns to VALID and writes resume:

FRA now 14% full, dest_1 = VALID
ARC_OK ROWS=450002

Same database, same user that was locked out a moment ago — now connecting and writing.

Don’t take my word for it — run it. The archiver-stuck lab puts Oracle Database Free in ARCHIVELOG mode with a 50 MB FRA, fills it until the archive destination goes to ERROR (ORA-19809), and asserts that a normal user is refused with ORA-00257 while SYSDBA still connects. Then it runs the RMAN backup-and-delete plus a size bump and asserts the destination returns to VALID and the same user writes again. If the outage doesn’t reproduce — or survives the fix — the run fails. Proven on every CI push.

The point isn’t the emergency; it’s that you should never have to run it. Once you’re back up: schedule the archive-log backup ( BACKUP ARCHIVELOG ALL DELETE INPUT on a cadence that keeps ahead of your redo rate), size the FRA for real workload plus a safety margin, and monitor V$RECOVERY_FILE_DEST percent-used with an alert well below 100% - 85% is a good line. The whole outage is a monitoring gap wearing an error number.

What teams get wrong

• rm on the archived logs. Deleting archived logs at the OS level frees space and silently breaks your backups: RMAN still thinks those logs exist, its recovery catalog is now wrong, and your next restore fails at the worst possible time. Always delete through RMAN (DELETE ARCHIVELOG, or DELETE ... INPUT on a backup), or at minimumCROSSCHECK ARCHIVELOG ALL afterward so RMAN knows what's really there.
• Deleting without backing up. Freeing the FRA by deleting archived logs you never captured unsticks the database and destroys your ability to recover to any point after your last full backup. Back up first, then delete — that’s whatDELETE INPUT does in one step.
• Turning off ARCHIVELOG mode to make it stop. NOARCHIVELOG makes ORA-00257 impossible because it makes point-in-time recovery impossible. That’s not a fix; it’s trading an outage for the inability to recover from the next one. Keep archiving on and fix the space.
• Sizing the FRA once and forgetting it. Redo volume grows with the workload. An FRA that held three days of logs last year holds eight hours during this year’s migration. Size it against current redo generation, not the day you built the database.
• No monitoring. ORA-00257 almost never arrives without warning — the FRA climbs through 80%, 90%, 95% over hours or days. A single alert onV$RECOVERY_FILE_DEST percent-used turns a 3 a.m. outage into a ticket you handle at 4 p.m.
• Assuming it’s always the FRA. Usually it is, but check V$ARCHIVE_DEST.ERROR - a full non-FRA archive filesystem, a permissions change, or an unreachable Data Guard destination produces the same ORA-00257 with a different root error. Fix the destination the error actually names.

What does ORA-00257 archiver error mean?

ORA-00257 means the database is in ARCHIVELOG mode and the archiver process (ARCn) cannot write the next archived redo log, so the database has run out of reusable online redo log space and is refusing new redo rather than overwriting redo it has not safely archived. Because writing any change requires redo, the database effectively stops accepting writes: SELECT and existing read-only work continue, but any session that tries to generate redo hangs or is turned away. The message ‘connect internal only, until freed’ (on current releases, ‘Connect AS SYSDBA only until resolved’) means privileged administrative sessions can still connect to fix the problem while ordinary sessions are locked out. It is a protective refusal, not a crash or corruption — no committed data is lost, and the database recovers fully once the archiver can write again.

Why is my Oracle database stuck with ORA-00257?

In the overwhelming majority of cases the archive destination is full — specifically the Fast Recovery Area (FRA), the fixed-size disk area Oracle archives into. Archived redo logs accumulate there at the rate the database generates redo, and if your backups do not also delete the archived logs they capture (or you have no archive-log backup), the FRA fills to its db_recovery_file_dest_size limit. ARCn then fails to create the next archived log with ORA-19809 (limit exceeded for recovery files), marks the archive destination ERROR, the online redo logs can no longer be recycled, and the instance refuses redo with ORA-00257. Less commonly the cause is a non-FRA archive directory that filled up, a permissions or path change on the archive location, or an unreachable Data Guard standby destination — all of which leave the archiver with nowhere to write. Check V$ARCHIVE_DEST.ERROR to see which.

How do I fix ORA-00257?

Fix it in two steps. First, get the database writable again by giving the archiver room: if the cause is a full FRA and you have disk headroom, ALTER SYSTEM SET db_recovery_file_dest_size to a larger value clears the stuck condition immediately because the archiver retries automatically as soon as space appears. Second, fix the real cause by reclaiming space the right way: use RMAN to back up the archived logs to a location outside the FRA and delete the inputs in one step (BACKUP ARCHIVELOG ALL DELETE ALL INPUT), which frees the FRA while preserving your ability to recover. Never delete archived logs with an OS rm, because RMAN’s view of them becomes wrong and future restores fail. After you are back up, schedule regular archive-log backups that delete input, size the FRA for the real workload, and monitor its percent-used so the next fill is a ticket, not an outage.

What is the difference between ORA-00257 and ORA-19809?

They are two ends of the same problem. ORA-19809, ‘limit exceeded for recovery files,’ is the error the archiver itself hits when it tries to write into a Fast Recovery Area that has reached its size limit; you see it in the alert log and in V$ARCHIVE_DEST.ERROR, and it is the root cause. ORA-00257, ‘archiver error, connect internal only,’ is the downstream symptom that ordinary user sessions see once the archiver is stuck and the database can no longer accept redo. Diagnosing from ORA-00257 alone tends to go in circles because it does not say why the archiver failed; the actionable error is ORA-19809 (or whatever error V$ARCHIVE_DEST shows for the failed destination), which points directly at the full recovery area.

Can I just delete archived logs to fix ORA-00257?

You can, but only through RMAN, and ideally only after backing them up. Deleting archived logs with an OS-level rm frees the space but leaves RMAN and its recovery catalog believing those logs still exist, which silently corrupts your recovery picture and causes future restores to fail; if you ever must delete at the OS level, run CROSSCHECK ARCHIVELOG ALL afterward so RMAN reconciles what is actually on disk. The correct approach is RMAN BACKUP ARCHIVELOG ALL DELETE ALL INPUT, which captures the logs to a backup location and then deletes the originals in one operation, freeing the FRA without sacrificing your ability to recover to a point in time. Deleting logs you have not backed up unsticks the database at the cost of losing recoverability for the period those logs covered — a trade you rarely want to make.

How do I prevent ORA-00257 from happening again?

Treat it as a monitoring and housekeeping problem, because that is what it is. Schedule regular RMAN archive-log backups that delete the input logs (BACKUP ARCHIVELOG ALL DELETE INPUT) at a frequency that stays ahead of your redo generation rate, so archived logs never accumulate faster than they are removed. Size the Fast Recovery Area (db_recovery_file_dest_size) for the real workload plus a safety margin, remembering that redo volume grows during migrations, bulk loads, and index rebuilds. Most importantly, alert on V$RECOVERY_FILE_DEST percent-used at a threshold well below 100% — 85% is a reasonable line — so the FRA filling shows up as a warning hours in advance instead of a 3 a.m. outage. ORA-00257 almost never arrives without a slow, visible climb first; the only reason it becomes an emergency is that nobody was watching the number.

Does ORA-00257 mean I lost data or the database crashed?

No. ORA-00257 is a protective refusal, not a crash or corruption: the database is deliberately declining to generate new redo because it cannot safely archive the redo it already has, and it does this precisely so that no committed transaction is ever lost. The instance stays up, existing committed data is intact, SELECT and read-only work continue, and SYSDBA can still connect. Only new writes are blocked, and only until you free the archive destination. Once the archiver can write again, the online logs recycle and everything that was hanging proceeds normally — there is nothing to recover and no data loss to repair. The error is the database protecting your recovery guarantees, which is the opposite of losing data.

Why can I connect as SYSDBA but normal users get ORA-00257?

That asymmetry is the whole point of ‘connect internal only, until freed.’ When the archiver is stuck, the database blocks the operations that generate redo so it does not overwrite unarchived logs, but it deliberately leaves a privileged door open so an administrator can get in and fix the problem — otherwise you would be locked out of the very database you need to repair. A SYSDBA session can connect and run the diagnostic queries and the fix (growing the FRA, backing up and deleting archived logs) because administrative connection and recovery operations are permitted; ordinary application sessions are refused the moment they try to write, because that write would require redo the database cannot currently accommodate. So SYSDBA access working while everyone else sees ORA-00257 is expected behavior, and it is your route to resolving the outage.

Archiver-stuck belongs to the same reliability story as the rest of Oracle’s recovery machinery: the archived logs whose accumulation caused this outage are exactly what RMAN needs to recover the database to a point in time, which is why you delete them through RMAN and never with rm; ARCHIVELOG mode is also what makes Flashback Database and guaranteed restore points possible; and it is a different failure from the undo-side ORA-01555 snapshot too old, which people confuse with it because both involve a long-suffering database and a cryptic number. ORA-00257 is the archiver protecting your recovery guarantees - read the real error (ORA-19809, a full FRA), free the space the right way, put a monitor on the recovery area, and then prove the whole cycle with the archiver-stuck lab, where a full FRA locks out every non-SYSDBA session and an RMAN backup-and-delete brings it straight back.

Originally published at https://uptimearchitect.com on September 23, 2026.