DEV Community: Uptime Architect

ORA-12514: The Database Is Up. The Listener Just Doesn't Know It.

Uptime Architect — Sun, 01 Nov 2026 13:41:46 +0000

The application can't connect. The error is one every Oracle DBA has seen:

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.

ORA-12514 means the listener answered and has no service by that name. Compare what the client asked for (the listener log records it) with what the listener has (lsnrctl services). A wrong name is a client fix; a missing registration is a database fix.

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:

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).
lsnrctl services on that host. Is the service listed? If yes, the client has the wrong name or the wrong listener.
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).
SHOW PARAMETER local_listener. Does it include the address the client is using?
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 uptimearchitect.com.

ORA-01652: Unable to Extend Temp Segment. Don't Just Add Space.

Uptime Architect — Wed, 28 Oct 2026 14:00:23 +0000

The open-orders report has run every morning for a year. Today it fails:

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 has TEMP_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, trust SQL_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: a CREATE 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 uptimearchitect.com.

I couldn't even read one row. The session that locked it was gone. So I built a ghost hunter for Oracle

Uptime Architect — Wed, 28 Oct 2026 01:52:57 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 4: Ghost in the Machine

select amt from gh_ledger where id = 1;

ORA-01591: lock held by in-doubt distributed transaction 5.30.71483

Not an update. A plain select. Oracle wouldn't even let me look at the row.

Two minutes earlier, a transfer of 40 between two of my lab databases had crashed halfway through its commit. (Oracle commits across two databases in two steps: both sides promise, then both commit. This one died between the steps.) I crashed it on purpose, with a test hook Oracle ships for exactly this (COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7'), because I wanted to see what a DBA is left with afterwards.

One side committed. The other had promised to commit, and then the session that made the promise was gone. Until somebody decides how that story ends, the row stays locked for everyone, and no session holds the lock, so there's nothing to kill. Oracle's recovery process would normally settle it once both databases can talk again. In my lab I switched that off, so the row waits. Nobody is coming back for it.

DBAs have a quiet name for things like this. Ghosts. For this week's theme I took the word literally: things that are still in the database after whatever owned them is gone.

Día de los Muertos isn't about getting rid of the dead. It's about saying who they were. So nothing here is removed quietly: every ghost gets its name, its date and an epitaph written into the report, and the report stays on disk after the ghost is laid to rest.

What I Built

Ghost Hunt finds what's still haunting your Oracle databases, and lays it to rest only when you say so.

Six kinds so far:

🕯️ a session whose user went home with a transaction open, while someone else waits on it;
⛓️ an in-doubt distributed transaction, like the one above;
📜 a database file on disk that the database stopped using;
⚰️ a dropped table still holding its space in the recycle bin;
🪦 an object left invalid by something it depended on;
🗝️ a contractor's locked account whose nightly job is still enabled.

SQL finds every ghost. Gemma 4, running locally on Ollama, writes the field notes: two lines on top, and an epitaph for each ghost. A Temporal workflow then waits, for up to twelve hours, for a person to approve each exorcism. Only then does it touch anything, and afterwards it hunts again to prove the ghost is gone.

This is the in-doubt ghost from the opening, as the report shows it:

### 2. ⛓️ Distributed transaction 5.30.71483 is prepared and nobody is coming back for it
lab-se2 · ORCLPDB1. Since 2026-10-27 23:48.

Here lies 5.30.71483, a prepared distributed transaction from 23:48.
It waits for a master that will never return.

The other side: lab-26ai/FREEPDB1 committed. Decision: commit force: the other side committed.

Exorcism, if you approve it:
commit force '5.30.71483';
begin dbms_transaction.purge_lost_db_entry('5.30.71483'); commit; end;

The epitaph is Gemma's. Everything else, including the decision, is code. Ghost Hunt reads both databases, matches the two halves of the transaction by its global id, and sees that the other side committed. So the only correct ending is to force this side to commit too. If no side has an outcome written down, it refuses to guess and tells you what to look up.

Demo

The video is one real run, start to finish:

the row nobody can read;
the hunt and its report;
I kill -9 the worker while the hunt waits for me;
I approve the exorcism anyway, while no worker is running;
I start the worker again. Temporal picks the workflow up where it was and lays three ghosts to rest: the session blocking someone, the prepared side of the transfer, and the committed side's leftover entry. Then it hunts again.

Then the row reads 60, which is 100 minus the 40 that left. The other database reads 140. The money that was in limbo is accounted for on both sides.

Code

pyaroslav / ghost-hunt

Finds the ghosts in your Oracle databases and lays them to rest only when you approve. Gemma + Temporal + Sentry agent tracing.

Ghost Hunt

Ghost Hunt finds what's still haunting your Oracle databases, and lays it to rest only when you say so.

A ghost is something still in the database after whatever owned it is gone: a session whose user went home with a transaction open, a distributed transaction that crashed halfway and now holds a row nobody can even read, a control file the database stopped using two weeks ago, a dropped table still holding its space, a contractor's locked account whose nightly job still runs.

SQL finds every ghost. Gemma, running locally, writes the field notes. A Temporal workflow waits, for hours if need be, for a person to approve each exorcism, and then hunts again to prove the ghost is gone.

### 2. ⛓️ Distributed transaction 10.9.199680 is prepared and nobody is coming back for it
`lab-se2` · `ORCLPDB1`. Since 2026-10-27 22:37
_Here lies 10.9.199680, a prepared distributed

…

View on GitHub

The Spirit of It: the one ghost I didn't plant

To have something to hunt, I planted ghosts in two lab databases running in Docker: Oracle 26ai Free and 12.2 Standard Edition 2. The first time I ran Ghost Hunt, it found one I hadn't planted. Here it is in the demo run's report:

### 5. 📜 control02.ctl (18 MB) is on disk but the database stopped using it
lab-se2 · CDB$ROOT. Since 2026-10-13 16:08.

Here lies control02.ctl, an 18 MB file from 2026-10-13.
It rests on the disk, though the database no longer needs it.

The hunter won't touch this one. Move it aside, keep it a week, then delete it.
Never delete a file the database still lists.

I went back through that database's alert log. On October 13 at 16:07, while the container was still creating the database, its setup script ran this:

ALTER SYSTEM SET control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl' SCOPE=SPFILE;

From that restart on, the database had one control file. The second copy, the one that exists in case the first is lost, was last written a minute later. It has sat on the disk for two weeks, looking exactly like a live control file: a spare kept faithfully for a database that stopped counting on it. It's the same database whose crash started my Week 3 entry, and I'd looked at it every day since without noticing.

Ghost Hunt doesn't touch files. Some ghosts you bury yourself.

How I Built It

1. SQL finds the ghosts, with an account that can only look

The hunter connects as c##ghost, a common user with CREATE SESSION and SELECT ANY DICTIONARY, and nothing else. There's one query per kind of ghost, run in each PDB (each database inside the container database). For example, a session that is idle, has an open transaction, and has others waiting on it:

select s.sid, s.serial#, s.username, s.last_call_et, t.used_urec,
       (select count(*) from v$session w where w.blocking_session = s.sid) blocked
  from v$session s join v$transaction t on t.ses_addr = s.saddr
 where s.type = 'USER' and s.status = 'INACTIVE' and s.last_call_et >= :idle

The queries never select machine, osuser or SQL text (on my lab, machine is my real hostname). Files are compared by listing the data directory (through docker exec) against v$datafile, v$tempfile, v$logfile and v$controlfile.

2. Code decides; the model doesn't

Ghosts are numbered by harm. Whatever is blocking someone right now comes first: the session others wait on, the row nobody can read. The in-doubt decision follows only an outcome that's already written down somewhere:

if a side committed, force the prepared side to commit;
if a side was already rolled back, force a rollback;
otherwise, don't decide at all. The database that decides (the commit point) never sits in prepared. So if every side the hunter can see is prepared, the decider isn't in the hunt, and any choice would be a guess.

The settled side's entry is the only written record of how the transaction ended, so it's purged only after its prepared partner has been forced, in the same run. Approve the committed side (#3 in the demo) without the prepared one (#2) and the hunter refuses: "approve #2 with it, so its partner is forced first."

Only two kinds of ghost can be exorcised by the hunter, because only for those can the right move be read off the database: a session that someone is waiting on, and an in-doubt transaction with a written outcome. A session that's idle with an open transaction but blocks nobody gets a note for its owner, not a kill. Everything else gets the SQL and a sentence, for a person to run.

3. Hours later, is it still the same ghost?

The approval can come six hours after the hunt. By then the user who went home may be back, may have committed, and may be halfway through something new in the very same session. So right before anything runs, the exorcist checks the ghost again:

for a session: the same sid and serial# (the session's number and which incarnation of it), the same transaction id, still idle, still blocking someone;
for an in-doubt transaction: the same entry, still in the same state.

If anything changed, nothing is run, and the report says "not touched: it changed since the hunt, so the approval no longer applies." Afterwards the databases are hunted again. If one can't be reached for that check, the report says the exorcism ran but couldn't be verified. It doesn't claim success it didn't see.

4. Gemma writes the words, and every line is checked

Gemma gets each ghost's name, a one-sentence description, the date, and the decision. It never sees SQL. It returns JSON: two summary lines and one epitaph per ghost. The prompt asks for "an old caretaker who has buried many databases: dry, kind, a little spooky."

Every line is checked before it goes into the report:

the epitaph must name its own ghost;
every number in it must appear in that ghost's facts;
every identifier (anything with an underscore or a dot, like GH_AUDIT_2019 or LAB.GH_RATES_TODAY) must appear in that ghost's facts;
the summary must name both databases, and its second line must name the ghost the code ranked first.

A line that fails gets one retry. Then a plain sentence from the facts replaces it, and the report says so. This is the pattern from my Night Shift entry, applied to every line instead of two.

5. Temporal waits, and survives

The workflow is hunt (one per database) → examine → write_story → deliver → wait → exorcise → hunt again → deliver. The interesting step is the wait:

await workflow.wait_condition(lambda: self.approval is not None,
                              timeout=timedelta(hours=a.wait_hours))

An approval is a Temporal signal, so the server keeps it even while no worker is running. That's exactly what the demo does: the worker is killed during the wait, the approval arrives anyway, and the restarted worker carries on from the wait. If nobody answers in twelve hours, the workflow ends and touches nothing.

The demo run's whole event history is in the repo, results/demo-history.json. The hunt and the report ran on worker 3143315. The three exorcisms and the second hunt ran on worker 3144782, the one I started after the kill.

The exorcist is a second account, c##exorcist, with just enough to kill a session and force or purge a transaction (the exact grants are in the README). That includes ALTER SYSTEM, which is not a small privilege, and exactly why the exorcist isn't the hunter. I found one of the grants the hard way: on 26ai the purge fails with ORA-41900: missing DELETE privilege on "SYS"."PENDING_TRANS$", which at least says exactly what it wants.

The tests run the whole workflow on Temporal's time-skipping test server, in CI, without a database:

only approved ghosts are touched;
a ghost that changed since the hunt is left alone;
twelve hours with no answer touch nothing. That test passes in under a second.

Four more cover the purge order, an unreachable database, a check that can't run, and the model being down.

6. Sentry sees the shape, not the ghosts

Every activity that touches a database or the model is a Sentry gen_ai.invoke_agent transaction, with execute_tool spans for the hunts and exorcisms and a chat span for Gemma. Each one carries a trace id derived from the workflow id, so a hunt that waits, loses its worker and resumes is still one trace.

What leaves the machine: the model name, token counts, timings, how many ghosts of each kind, and errors cut down to their ORA- code. No SQL, no object or user names, no prompt, no answer. (Sentry's servers see the IP address an event comes from, like any web server. Left alone, Sentry turns it into a city. So Ghost Hunt sends 0.0.0.0 as the user's address, and the project has "Prevent Storing of IP Addresses" on.) The span says it plainly:

I didn't get that right the first time. The first trace I opened had a subprocess span I never wrote. Sentry instruments the standard library by default, and its description was the full command line, docker exec w2-se2 find /opt/oracle/oradata …. Harmless in a lab, and exactly the kind of thing I'd promised not to send. Now that integration is off, a filter drops any span that isn't gen_ai.*, error stack frames lose their file paths (a home directory names its user), and tests check all three.

Which Gemma? I read every line

The checks catch an epitaph that names the wrong ghost or invents a number. They can't catch a sentence that uses the right names and numbers and still says something untrue.

So I took one real hunt (7 ghosts) and asked two sizes of Gemma 4 for its report 20 times each. Then I read all 280 epitaphs and 40 summaries against the facts:

Same hunt, 20 runs each	gemma4:e4b	gemma4:26b
Epitaphs and summaries passing the automatic checks	160 / 160	160 / 160
Epitaphs with a false claim	24 / 140	1 / 140
Summaries (two lines each) with a false claim	5 / 20	0 / 20
Median seconds per report (one call)	1.05	1.32

Both models passed every check on every run. Measured only by the checks, they'd be a tie. Read by a DBA, e4b's mistakes are the kind that sound right:

Here lies GH_AUDIT_2019, a table dropped on 2026-10-27. It still holds 7.0 MB of memory.

It's disk space.

Here lies 4.4.159581, who finished a distributed transaction on 2026-10-27 22:37. It rests, having already been purged.

It hadn't been. Purging it is the exorcism, and nobody had approved anything.

Here lies session 217, which began its work at 2026-10-27 22:37. It still waits for another session to finish its task.

Backwards. It's the one being waited on.

The 26B model made one mistake in 140 (the same "of memory"). It costs about a quarter of a second per report once loaded, so it's the default. Every line from all 40 runs is in the repo (results/eval.json), with my count and criteria in results/claims.md.

What I take from it: a validator tells you the model stayed inside the facts. It doesn't tell you the model understood them. That's also why, in Ghost Hunt, a wrong epitaph can make a report wrong in tone, but never makes the hunter touch the wrong thing. The ghosts, their order, the decisions and the SQL never come from the model.

Why Does Open Innovation Matter?

Everything Ghost Hunt reads is what a DBA can't paste into a hosted chatbot: session lists, transaction ids, user names, file paths. Here, Gemma runs on the same machine as the databases. Temporal is one binary with a SQLite file. The only thing that can leave is the Sentry metadata above, and that's optional. A hunt has no API bill.

Open weights mattered in a second way, too. The model choice above came from running the same input 40 times and reading every answer. With an open model on my own GPU, that's a few minutes and no bill. I can pin the exact model and rerun the comparison whenever a new Gemma comes out. Anyone with Ollama can repeat it from results/ghosts.json, no Oracle needed.

What it doesn't do (yet)

It hunts two lab databases. The queries are standard dictionary views, and I'd expect them to work on any 12.2+ CDB, but I've only run them on these two.
The in-doubt ghost needed two lab settings to stay put:
- distributed recovery is disabled on both databases, or Oracle's recovery process (RECO) quietly resolves it within seconds;
- the database link goes from 12.2 to 26ai, because the other direction fails with ORA-28267.
The file check needs to list the data directory, through docker exec here.
The false-claim count is one fixture, read by one person: me.

The row

It reads 60 now. The 40 that left arrived on the other side, the transaction that carried it has an epitaph in a report on my disk, and the control file is still waiting for me to bury it.

Prize Categories

Best Use of Gemma. Gemma 4 26B, local on Ollama, writes the summary and every epitaph inside line-by-line checks, and was chosen over e4b by reading 280 epitaphs.
Best Use of Temporal. The exorcism waits on a signal for up to twelve hours, survives its worker being killed, and touches only approved ghosts, re-checked, in a safe order.
Best Use of Sentry Agent Tracing. One metadata-only gen_ai trace per hunt, across a worker restart, including the leak I found in my own trace and the test that keeps it out.

Happy Halloween, and to everyone keeping an ofrenda this weekend: may your databases remember only what they should.

My database died on Tuesday. I found out on Sunday. So now a local model reads my Oracle logs while I sleep

Uptime Architect — Mon, 26 Oct 2026 00:09:57 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 3: Slow and Steady

On Tuesday afternoon at 18:39 UTC, a background process in one of my lab databases died:

2026-10-20T18:39:10 Instance Critical Process (pid: 7, ospid: 3437, MMAN) died unexpectedly
                    PMON (ospid: 3425): terminating the instance due to error 822
2026-10-20T18:39:13 Instance terminated by PMON, pid = 3425

MMAN manages the database's memory. When it dies, Oracle takes the whole instance down with it, and that's what happened. Then nothing happened for five days. No alert, no email. The alert log doesn't write anything once the database is gone. I found out on Sunday evening, by accident, while looking for something else.

Every DBA knows the cure, and nobody actually does it: read the alert log every morning. It's three hundred lines a night of log switches, resizes and scheduler windows. Somewhere in there might be the one line that matters. Some mornings there isn't one, and the morning there is, you've stopped reading.

So I built a sloth to do it for me.

What I Built

Night Shift reads your Oracle alert and listener logs overnight and leaves you two lines in the morning. Everything else stays in a list underneath, in case you want it:

# Night Shift

> lab-26ai had an out of space issue; lab-se2 is down. lab-26ai also saw 37 checkpoint issues and 1 deadlock.
> Start lab-se2 immediately, then check the alert log entries from before it stopped.

## lab-26ai: up
2026-10-24 23:01 to 2026-10-25 23:01 UTC · 303 log lines, 263 routine

- 🔴 Out of space in a tablespace (ORA-01653), 2026-10-25 22:47 UTC. Add or extend a datafile, or find what grew.
  FREEPDB1(3):ORA-01653: unable to increase tablespace NS_TINY [FREEPDB1] by 1MB during insert or update on table LAB.NS_FILL
- 🟡 Checkpoint not complete, 37× 2026-10-25 01:00 UTC → 2026-10-25 21:06 UTC. Redo logs are too small or too few for the load.
- 🟡 Deadlocks (ORA-00060), 2026-10-25 22:47 UTC. Read the trace file: it names both sessions and the SQL.

## lab-se2: DOWN
- 🔴 Database is not running now. no pmon process for ORCLCDB; last alert log entry 2026-10-20 18:39 UTC

Night Shift · Temporal run night-20261025-230121 · 1 s · summary by gemma4:e4b

That's a real run from tonight, trimmed a little, against two lab databases on my desktop: Oracle 26ai Free and 12.2 Standard Edition 2. To give it something to find, I filled a 16 MB tablespace and caused a deadlock on the 26ai one. The SE2 one was still dead from Tuesday (more on that at the end).

It's set up once and then left alone. A Temporal Schedule runs it at 6 AM. The brief lands in a Markdown file and a desktop notification. The model runs locally in Ollama. Night Shift never connects to the database: it reads the log files and checks the process list, and nothing leaves the machine.

Demo

Code

pyaroslav / night-shift

Reads your Oracle alert and listener logs overnight and leaves a two-line brief for the morning. Gemma 4 on Ollama + Temporal, all local.

Night Shift

Night Shift reads your Oracle alert and listener logs overnight and leaves a two-line brief for the morning. It runs on your own machine: Gemma 4 on Ollama writes the brief, and a Temporal workflow makes sure it arrives.

I built it because my lab database died on a Tuesday afternoon and I found out on Sunday night.

# Night Shift
> lab-26ai has an out of space tablespace; lab-se2 is down; lab-offsite logs are unreadable.
> Start lab-se2 and review its logs from before it stopped.

## lab-26ai: up
2026-10-24 22:56 to 2026-10-25 22:56 UTC · 302 log lines, 262 routine

- 🔴 Out of space in a tablespace (ORA-01653), 2026-10-25 22:47 UTC. Add or extend a datafile, or find what grew.
- 🟡 Checkpoint not complete, 37× 2026-10-25 01:00 UTC → 2026-10-25 21:06 UTC. Redo logs are too small or too few for the load.
-

…

View on GitHub

How I Built It

The obvious version of this project is one line of code: paste last night's alert log into a model and ask for a summary. I built that version too, as the baseline, and it's the reason the real one looks the way it does.

Rules sort the night, the model writes two lines

logs.py splits the alert log into timestamped entries. It handles 12c+ ISO timestamps and the old 11g Tue Oct 20 18:39:10 2026 style, plus the listener log. Each entry goes through about twenty plain patterns, written so a DBA can read the file and agree with every line:

Critical: instance terminations, dead background processes, ORA-600/7445, block corruption, a stuck archiver, a full tablespace, missing online redo logs, ORA-4031, running out of processes, the listener losing the service.
Look today: deadlocks, checkpoint not complete, ORA-1555, failed scheduler jobs, connection timeouts, failed logins, swapping.
Routine: log switches, resizes, scheduler windows, service updates. These are counted, never reported.

Repeats are folded together. A job that fails every ten minutes is one finding with a count. One crash that writes four alert log entries ("terminating", two state dumps, "terminated") counts as one incident, not four. An ORA- code that matches no rule still shows up as "Other Oracle error", so a problem I didn't think of can't quietly disappear. And if you've decided to live with something, like the Statistics Advisor job that fails every ten minutes on a fresh 12.2 CDB, there's a mute list.

A deadlock line in the alert log only says "see the trace file", and nobody opens trace files at 7 AM. So for ORA-00060, Night Shift opens the trace itself. It reads the deadlock graph to tell the kind of deadlock apart: TX locks held and wanted in exclusive mode mean two sessions updated the same rows in opposite order; a TM lock usually means a foreign key without an index; share-mode waits point at duplicate-key inserts or bitmap indexes. Then it pulls each session's user and SQL. The client machine and program name stay out of the brief:

- 🟡 Deadlocks (ORA-00060), 2026-10-25 22:47 UTC. Row lock deadlock: two sessions updated the same rows
  in opposite order. Make the application touch rows in one order (e.g. by key), or commit sooner.
  - session 230 (LAB): update ns_dl set v = 2 where id = 1
  - session 42 (LAB): update ns_dl set v = 2 where id = 2

Two checks look at what isn't in the log. Is pmon running? And did the alert log write anything at all? A database that crashed on Tuesday writes nothing on Wednesday, so a silent night is a critical finding, not a quiet one. My first run of the pmon check told me lab-26ai was down. It wasn't: since 23ai, the process is called db_pmon_FREE, not ora_pmon_FREE. Both names are checked now.

Then Gemma 4 (e4b) gets the night as a small JSON digest (about 500 characters per database), not the log, and writes exactly two lines. Its answer is checked before anyone sees it:

exactly two lines;
every number in them has to appear in the digest;
every database with a critical finding has to be named.

It gets three tries, and then a plain template writes the lines instead. The list under the two lines never comes from the model, so even a bad summary can't hide an outage.

Temporal makes sure it arrives

A morning brief that silently doesn't arrive is worse than no tool at all, because by then you've stopped checking yourself. That's why it's a Temporal workflow and not a cron job:

Each database is read in its own activity, with retries at 15 s, 30 s and 60 s. A host that stays unreachable doesn't crash the run. It becomes a finding: "Couldn't read the logs. The night is unknown."
If Ollama is down all night, the summary activity fails three times and the template writes the brief instead.
If the worker dies halfway, the run picks up where it stopped as soon as a worker comes back. Steps that already finished aren't repeated.
A Temporal Schedule starts it at 06:00 America/Chicago, with the overlap policy set to skip.

All of it runs locally. night-shift server starts Temporal's single-binary dev server with a SQLite file, so the history survives a reboot, and two systemd user units in the repo bring the server and worker back up afterwards. If the machine is asleep at 6 AM, the Schedule's catch-up window starts the missed run when it wakes up.

Two of those promises (an unreachable host, and no model all night) have a test on Temporal's time-skipping test server. It runs in about a second in CI. For the rest, I tested by hand: I added a third "database" whose container doesn't exist. I started a run, killed the worker with kill -9 twenty seconds in, and started it again thirty seconds later:

The two real databases were read in under a second. The missing one was retried four times over 1 m 45 s, across the restart. Then Gemma wrote the summary and the brief was delivered, with the missing host as the third critical finding. The first time this happened wasn't planned. My first worker crashed on startup: Temporal's workflow sandbox doesn't allow Path.resolve() at import time. The run I'd started just waited in the queue for ten minutes. When I fixed the worker, the run finished as if nothing had happened.

Does the brief say what matters?

I replayed 22 nights from the two lab databases' logs (Oct 13 to tonight), each one the 24 hours up to 6 AM Central. I compared Night Shift with the one-line version: the same night's raw alert log, the same model, the same two-line instructions. The events that must be in the two lines were found with plain grep on the raw log, independent of my rules: an instance died, a tablespace filled, a deadlock, a missing online redo log, or an alert log silent all night. That gave 8 events across 5 nights.

gemma4:e4b, 22 nights	Night Shift	Raw log, same model
Must-report events named in the two lines	7 / 8	6 / 8
Kept to two lines	21 / 21	17 / 21
Nights with numbers that aren't in the log	0	7 (4 of them wrong counts)
What the model reads (whole prompt)	~1,200 characters	up to 1.7 M characters before the cut

What the numbers hide:

The raw log is good on small nights. On the night lab-se2 died, there were only four entries, and it wrote the better line: "Instance terminated at 18:39:13 due to MMAN dying unexpectedly, triggering PMON error 822." Night Shift's line said the instance terminated, but not why. The why is in the list underneath.
On big nights it stops following instructions. Four nights didn't fit the 32K-token context I gave it, so Ollama cut them down to about 16K tokens and most of the night was lost. On all four it ignored "two lines" and wrote a multi-page essay with headings and a priority table. On one of them, the night's only deadlock was in the part that got cut, so the model never saw it.
It can't count. On four different nights it told me how many log switches there had been: 54, 33, 87 and 45. The real numbers were 61, 69, 58 and 77. That's four for four wrong, and each one sounded completely sure of itself. The other three nights' numbers are rounded file sizes it converted from KB, values from SQL it wrote itself, and the next log sequence after the window. Night Shift's counts come from Python. The model only gets to repeat them, and the number check rejects anything it makes up.
It can't see silence. The night after the crash, there was nothing to send it.
Night Shift's miss: on lab-se2's first night, the two lines named the shutdown, the missing redo log and 17 job failures, but not the 2 deadlocks. They're in the list, but not in what you'd read at 7 AM.

The honest caveats:

These are lab logs, and the must-report events are few.
One of the two "instance died" nights is a shutdown abort I ran during setup.
The tablespace was filled on purpose.
I changed the prompt once after the first replay. The two lines kept skipping deadlocks, and Gemma was writing "as per the finding's 'do' field". That first run scored 6/8 and is in the repo next to the second. I also loosened one scoring pattern from "silent" to "silen" after Gemma wrote "alert log silence", which gave Night Shift one of its 7.
A rerun with the final code, which adds the restart rule, scored 8/8 (results/replay-v3.json). The two lines vary a little from run to run, so the table keeps the lower number.

Why Does Open Innovation Matter?

An alert log is a map of someone's infrastructure. It has host names, file paths, user names, SQL fragments, and the exact times their systems fell over. A DBA can't paste a client's into a hosted chatbot. I wouldn't want to give a SaaS agent a login to production just to read it, either.

The open pieces are what make this possible:

Gemma 4 on Ollama runs on the same desktop as the databases, at no cost per night, with no internet needed.
Temporal is open source, so the durable part runs locally as one binary and a SQLite file. Nothing is hosted anywhere, and you still get retries, resume and schedules.
I can see why the model said what it said. The exact digest it read is right there. When it's wrong, I fix a rule or the prompt instead of hoping the next model version is better. A closed API wouldn't have let me run the raw-log baseline on 22 nights for free either, and that baseline is what told me where the model needs help.

The Lazy Payoff

Reading two alert logs properly takes a good ten minutes on a busy night, and nothing at all on the nights you skip it, which is how I lost five days. Now it's two lines with my coffee, and the full list only when a line is red.

Its first real run told me what I'd missed all week: lab-se2 is DOWN, last alert log entry Tuesday 18:39 UTC. So I started it. The next brief had moved on by itself:

## lab-se2: up
2026-10-25 00:03 to 2026-10-26 00:03 UTC · 90 log lines, 85 routine

- 🟡 Instance started, 2026-10-26 00:02 UTC. Check the restart was planned; the entries just before it say why it stopped.

It also points at the listener's "address already in use" lines from the restart, which are worth a glance and nothing more. The Temporal Schedule fires for the first time at 6:00 tomorrow morning, and this is what will be waiting:

Tonight, the sloth is on shift.

Prize Categories

Best Use of Temporal: the brief is a Temporal workflow with one activity per database, retry policies, and a fallback when the model is down. A host that can't be reached turns into a finding instead of a crash. A Temporal Schedule runs it at 6 AM. I tested it by killing the worker mid-run and pointing it at a host that doesn't exist (timeline above). Everything runs locally on Temporal's open-source dev server.
Best Use of Gemma: Gemma 4 (e4b) runs locally in Ollama and writes the two lines from a compact digest, checked for invented numbers and missing databases. The replay compares it with the same model reading the raw log, night by night.

Checkpoint Not Complete: Every Commit Waits, and the SQL Is Fine

Uptime Architect — Sun, 25 Oct 2026 20:49:26 +0000

The nightly batch used to finish in twenty minutes. Now it takes an hour, and during that hour the application stutters: every few seconds, every session that writes anything (an order, a login timestamp, a session audit row) freezes for a second or two, then carries on. The SQL hasn't changed. The plans haven't changed. CPU is fine, the storage team says the disks are fine, and the alert log has the same two lines over and over:

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 for INACTIVE.
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, set FAST_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 or ACTIVE 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 uptimearchitect.com.

Same SQL, Fast for One Value, Slow for Another

Uptime Architect — Wed, 21 Oct 2026 13:46:44 +0000

The order-history screen is fast for every customer except one. Or it's fast all morning and crawls all afternoon. Or it was fine for months and became slow the night after a restart, with nobody deploying anything. You pull the statement and run it yourself with the problem value, and it comes back instantly. The SQL is identical. The data hasn't changed. The plan in the execution history is the same plan it always uses.

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 the bind 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. See SQL 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 its own 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 uptimearchitect.com.

A client sends you an AWR report under NDA. I built a second opinion that never leaves your laptop

Uptime Architect — Sun, 18 Oct 2026 17:16:29 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 2: Side Hustle

Every Oracle DBA who freelances knows this email. "The system was slow yesterday between 2 and 4. AWR attached. Can you take a look?" Attached is a 200-page HTML report covering every wait event, every statement and every segment the database touched. The client is paying by the hour, and the report is covered by an NDA. It holds their SQL text, table names and host names.

The first hour on a report like that is always the same triage. Is the problem parsing, I/O, locking, redo, or one bad statement? Load Profile, Top Events, SQL ordered by gets, back to the events, then the instance activity stats. It's skilled work, but it's routine. And the obvious shortcut is off the table: you can't paste a client's AWR report into a hosted chatbot.

So I built Second Opinion. It's a first-pass AWR and Statspack reviewer that runs entirely on your own machine, with an open model. Once the model is loaded, it takes under a second on my desktop GPU to rank the likely root causes, show the numbers behind each one, and draft a short note for the client. Every number in that note is checked against the report before you see it.

In short: on 18 held-out lab reports with faults I injected myself, Second Opinion named the cause in 17 of 18. Oracle's own ADDM got 7/9 of the AWR ones, and Standard Edition can't run ADDM at all. Gemma 4 given the raw report got 10/18. Neither model ever quoted a number that wasn't in its input. I also fine-tuned Qwen3-8B on Tinker to rank the causes with no rules at all. Untuned it got 11/18; tuned, 16/18, with the top cause right on every run, and it runs locally in Ollama.

What I Built

Second Opinion is a command-line tool and a small Python library:

$ second-opinion awrrpt_1_17_18.html          # AWR, as awrrpt.sql writes it
$ second-opinion sp_10_11.lst                 # Statspack, any release
$ second-opinion pasted.txt                   # the sections a colleague pasted into a ticket

It reads the report and runs the checks a DBA runs in their head. Gemma 4 then writes up what those checks found:

Statspack report · ORCLCDB 12.2.0.1.0 · 13-Oct-26 16:58:47 to 17:05:47 (7.0 min, DB time 27.71 min)

**High Hard Parse Rate Indicates Inefficient SQL Usage**
Likely causes, most important first: Hard parsing (literal SQL, no binds)

## Note for the client
The Statspack report indicates a significant performance issue primarily driven by hard parsing.
The evidence shows that 100.0% of parses are hard parses, with 8811.8 hard parses per second. This
high rate is contributing to the high wait time on library cache: mutex X, which accounts for 39.5%
of DB time. To address this, first, review the application code to ensure all SQL statements
utilize bind variables instead of literals. ...

## Findings (computed from the report, not by the model)
- Hard parsing (literal SQL, no binds) (score 1.0): Most parses are hard parses...
  hard_parses_per_sec=8811.8, parses_per_sec=8812.8, hard_parse_pct=100.0,
  shared_pool_event=library cache: mutex X, shared_pool_event_pct_db_time=39.5

_Numbers checked: 3; every number in the note was found in the report. Model: gemma4:e4b. 0.6 s, all local._

That's a real run on one of the held-out reports. It isn't perfect: the note's second suggestion, about indexing, has nothing to do with hard parsing. That's exactly why the output is a draft for a DBA, not something to send unread.

Who pays for this:

Freelance and contract DBAs. They bill roughly $90–180 an hour, and on Fiverr an "AWR/Statspack performance review" package sells for around $220. Each report takes 30–60 minutes of triage before the real work starts. A tool that does the first pass, can't leak the report, and gives a draft client note gets that hour back on every engagement. Even one recovered billable hour a month would make $15–20 a month an easy sale. I'd price it as a one-time license per consultant, because "runs on your laptop" and "subscription" sit awkwardly together.
Shops on Standard Edition. SE and SE2 can't license the Diagnostics Pack, so they have no AWR and no ADDM. Statspack is what they have, and often nobody reads it. Second Opinion treats Statspack as a first-class input, and on Standard Edition it is the only automated reviewer there is.
Managed-service teams that get a "slow yesterday" ticket at 2 a.m. and want a first read before they wake the senior DBA.

Demo

A two-minute narrated walkthrough: a real report, the review, the Sentry traces, and the Tinker before/after.

Code

pyaroslav / second-opinion

A first-pass Oracle AWR/Statspack review on your own machine, with a local open model (Gemma 4) and a check that rejects invented numbers.

Second Opinion

Second Opinion gives a first-pass review of an Oracle AWR or Statspack report. It runs on your own machine with a local open model, Gemma 4 via Ollama, so the report never leaves the box.

This is a real run on one of the held-out lab reports (reports/test/hard_parse-statspack-*):

$ second-opinion statspack.txt
# Second Opinion: statspack.txt
Statspack report · ORCLCDB 12.2.0.1.0 · 13-Oct-26 16:58:47 to 13-Oct-26 17:05:47 (7.0 min, DB time 27.71 min)

**High Hard Parse Rate Indicates Inefficient SQL Usage**

Likely causes, most important first: Hard parsing (literal SQL, no binds)

## Note for the client

The Statspack report indicates a significant performance issue related to SQL execution. The primary
finding is a high rate of hard parses, with 100.0% of parses being hard parses. This is evidenced by
the high hard_parses_per_sec of 8811.8 and the associated wait event library cache: mutex X
consuming 39.5% of

…

View on GitHub

How I Built It

Don't hand the model the whole report

My first attempt was the obvious one: give Gemma 4 the AWR report and ask what's wrong. An AWR report for an 18-second window is about 18,000 tokens of text, and a real one-hour report is far bigger. On my held-out set, Gemma reading the raw report found the cause in 10 of 18. It reads alarming-looking wait events as problems on healthy systems, and it misses every full table scan, because the evidence for a full scan is spread across three sections (SQL ordered by gets, instance activity, segments by table scans).

So the model never reads the report. The work is split three ways:

Parsers (awr.py, statspack.py, text.py) turn AWR HTML, AWR text, Statspack from any release, or a few pasted sections into one small dict: load profile, top events, time model, a few instance activity stats, top SQL per category, and segments. Statspack and AWR text are fixed-width, so column names are read from the header above each dashed rule. Pasted text often has its whitespace mangled, so rows are read token by token: a name, then numbers. On all 32 lab runs, the text parser gives exactly the same findings as the HTML parser.
Rules (rules.py, about 150 lines) are the checks I'd run by hand: hard parsing, full scans (from logical reads, or from db file scattered read when the scans come from disk), row locks, commit storms, NOCACHE sequences, one CPU-heavy statement, and Resource Manager CPU caps. Each fires with the numbers that made it fire. A materiality gate stops a large percentage of an idle database from becoming a finding: "74% of DB time is log file sync" means nothing at 0.1 active sessions.
Gemma 4 (e4b, local in Ollama, about 160 tokens per second on my GPU) only sees the findings and a little context. It ranks the causes, writes a headline and drafts the client note.

A model that can't invent numbers

A DBA can live with a model that writes clumsy prose. A model that invents a statistic, in a note going to a client, is a disaster. So every number in Gemma's note is checked against the numbers it was given, allowing for rounding and thousands separators. If any number isn't there, the note goes back once with the offending numbers listed. If a number still can't be found after the retry, it is printed in a warning, not quietly dropped.

On the held-out set, neither Gemma reviewer quoted a number that wasn't in its input, and Second Opinion's retry never fired.

Testing it against faults I injected myself

You can't evaluate a diagnosis tool on reports where nobody knows the answer, so I built a fault lab. There are two disposable Oracle databases in Docker on my workstation:

Database	What it produces
Oracle AI Database 26ai Free 23.26.3	AWR + ADDM (Diagnostics Pack included in Free)
Oracle Database 12.2 Standard Edition 2, `control_management_pack_access=NONE`	Statspack only

lab/faults.py runs a workload with a known fault for 5–7 minutes: literal SQL, a missing index, sessions fighting over the same rows, commit-per-row, a NOCACHE ORDER sequence, a cartesian join, or a healthy light load. It takes a snapshot before and after, then saves the AWR, ADDM or Statspack report a DBA would get.

The rules were written while looking at a 14-run dev set. Then I wrote a held-out set: the same causes, but different SQL, different tables and different session counts, plus two runs with two faults at once. I scored it once.

Held-out, 18 runs. A reviewer passes if the injected cause is in its top two. With two faults at once, both have to be in its top three.

Reviewer	AWR (9)	Statspack (9)
Oracle ADDM	7/9	not available on SE2
Gemma 4, raw report	5/9	5/9
Rules alone	9/9	8/9
Second Opinion	9/9	8/9

What the misses taught me:

ADDM found the right SQL statement in both full-scan runs, but its advice was "Run SQL Tuning Advisor", which needs the Tuning Pack. It never said that the query full-scans a 400,000-row table with no index on its where column.
- My first ADDM scoring used keyword matching and gave it 5/9. When I read the ADDM reports by hand, two "misses" were findings titled "Hard Parse" and "Sequence Usage", mislabelled because the text nearby mentioned CPU. I switched to mapping ADDM's finding titles, and 7/9 is the corrected figure.
Our one miss is the NOCACHE sequence on 12.2 Statspack. That report shows the commit pressure but no sequence wait in its top events. I left the rules as they are rather than tune them on the test set.
Lab caveats. In the healthy AWR run, the light update workload hit two genuine ORA-00060 deadlocks. Second Opinion still called it healthy and listed row locks below the significance line. 26ai Free also caps its own CPU through Resource Manager, so busy runs show resmgr:cpu quantum; that's real for that database, but an artefact of Free.

Real reports from the wild

Lab faults are tidy. To see how it copes with what people actually paste, I collected ten AWR and Statspack reports posted publicly on the OraFAQ forums and Jonathan Lewis's blog. Each has an expert's diagnosis in the thread, and all ten are text, from Oracle 9i to 11g, and often only a few sections.

Two things broke at first:

I had no AWR-text parser, so I wrote one.
My rules had never seen db file scattered read, because in the lab every table fit in memory. On real systems that's the textbook sign of full scans from disk. I added that check, plus one that says "this wait dominates and nothing explains it" instead of calling such a system healthy.

After that, the rules matched the expert's diagnosis on 7 of the 10. The other three were two excerpts that left out the SQL sections the diagnosis depended on, and one log-switch problem just under my threshold. I wrote those two checks after reading these cases, so this is a set of worked examples, not a test score.

Thread	Report	Expert's call (paraphrased)	Second Opinion	Match
Statspack report: suggestion	Statspack	Full scans of a big table (scattered reads)	full scans	✓
statspack report	Statspack	Scattered reads on top: the SQL is full-scanning	full scans	✓
Upgrade Argh	AWR text	Small tables' scans turned into direct path reads after an upgrade	full scans	✓
High System I/O wait class	AWR text	log file sync dominates (slow redo writes vs. too many commits debated)	commit storm, other	✓
Fast now, fast later	Statspack	Single-block reads grow as the data grows	other: `db file sequential read` dominates	✓
Network waits from AWR	AWR text	Nothing to fix: network waits are a tiny share	healthy	✓
Reading AWR	AWR text	Mostly CPU, no instance-level problem	healthy	✓
NOLOGGING in 11gR2	AWR text	Log switches too frequent (checkpoint incomplete, ~19%)	healthy	✗
Analysing Statspack 13	Statspack excerpt	CPU-bound on logical I/O from scans; needs the SQL sections	healthy	✗
Analysing Statspack 5	Statspack excerpt	Repeated single-row deletes each doing a short full scan	healthy	✗

The three misses are honest misses. Two are blog excerpts where the answer lives in SQL sections that weren't quoted, and a tool that only sees the Top 5 events can't find it there either. The third is a real configuration problem that sits below my 25% threshold.

Fine-tuning a small model on Tinker

The rules in Second Opinion are hand-written, and I wrote them knowing which faults I'd inject. So the fair question is whether a small open model could learn that triage itself from the parsed numbers, with no rules at all. Tinker let me try that for about the price of a coffee.

Data. I left two generators running overnight against both lab databases: 524 short runs with random faults (one at a time, or two at once a quarter of the time), random session counts and 90–300 s windows. They use their own tables, SQL and sequences, so nothing overlaps the held-out set. A label is a fault that actually reached the report. I dropped 52 runs where the injected fault never showed. In one, for example, a literal-SQL flood had aged the CPU-heavy statement out of the shared pool before the snapshot, so no report could show it. That left 425 runs to train on and 61 to validate on (47 generated plus my 14 dev runs). The 18 held-out runs stayed untouched.

Task. The input is the parsed report, trimmed to what triage needs. The output is JSON ranking the likely causes. The base model and the tuned one get the same prompt, with Qwen3's thinking turned off.

Training. LoRA rank 32 on Qwen3-8B, 3 epochs, 81 steps, with a checkpoint saved after each epoch. Training used 3.66M tokens, about $1.61 at list price. With the early test run and the sampling for evaluation, the whole experiment cost roughly $2 of a $10 credit. I picked the epoch on the validation set:

Qwen3-8B, validation (61 runs)	Pass	Top cause right	False causes
Base, untuned	40	31	136
Tuned, epoch 1	55	61	0
Tuned, epoch 2	51	57	4
Tuned, epoch 3	57	61	1

Then I scored the base model and epoch 3 once on the 18 held-out runs:

Qwen3-8B, held-out (18 runs)	AWR	Statspack	Top cause right	False causes
Base, untuned	5/9	6/9	11/18	38
Tuned	8/9	8/9	18/18	0

"Pass" is the same rule as the main table: the cause in the top 2, or both causes in the top 3 when two were injected. A false cause is a label that wasn't injected. I don't count cpu_cap as a false cause, because Free really does throttle itself, and the training labels include it when the report shows it.

The base model's problem is easy to see: it names three causes every time, so it is never sure and often wrong. After tuning it names one or two, and the first one is right on every held-out run. Its two misses are both the full-scan + commit-storm pair, where it names the commit storm and drops the full scan.

This doesn't replace the rules. The rules plus Gemma pipeline is still the tool, at 17/18 with a written note and every number checked. But a tuned 8B model with no rules reached 16/18 on the same runs. That tells me the triage isn't only in my head, and a model can learn it from lab data I can make as much of as I like.

So it went into the tool as a second, independent read. second-opinion report.html --triage-model second-opinion-qwen3 adds the tuned model's own ranking under the review, checks the numbers in its evidence the same way, and says whether it agrees. On the held-out set it catches the review's one miss: on the 12.2 Statspack sequence run, the rules say "committing too often", and the tuned model says "sequence contention", which is right. It also disagrees on the healthy AWR run with real deadlocks. A disagreement isn't a verdict; it's a reason to read both before writing to the client.

Running it locally. The tuned model only counts for this use case if it runs offline, so I exported the adapter to Ollama. That was harder than expected:

Ollama 0.35 no longer takes a LoRA adapter in a Modelfile.
Merging into the stock 4-bit model and saving it back at 4-bit lost the tuning.
A base I converted myself from the Hugging Face weights gave repeating garbage once merged.

What works is merging the adapter into the exact qwen3:8b weights Ollama already has and stopping at 8-bit (lab/tune/export.py). The local copy passes 16/18 too, and it agrees with Tinker's top cause on 17 of 18 runs, at about 0.5–0.7 s per report. The adapter is on Hugging Face: pyaroslav/second-opinion-qwen3-8b-lora, in PEFT and GGUF form. Where it differs, it misreads the healthy AWR run as row locks (the run with the real deadlocks), and it gets one full-scan + commit-storm pair that the Tinker copy missed.

Watching the agent with Sentry

Tracing an agent whose whole selling point is "nothing leaves your laptop" is a bit of a contradiction. So the first design decision was what Sentry may see. With SENTRY_DSN set, each review becomes one gen_ai.invoke_agent transaction with these child spans:

gen_ai.execute_tool for parsing;
gen_ai.execute_tool for the rules;
one gen_ai.chat per model call, including the retry.

What leaves the machine: span names, durations, the model name, token counts, which causes the rules found, and how many numbers failed the check. There are no prompt or response attributes, send_default_pii=False, and tracing is off unless you turn it on. Sentry's own agent view confirms it:

Setup. I wrapped both reviewers in the benchmark as agents, "second-opinion" and "raw-baseline", and ran all 18 held-out reports through each.

Performance (tokens, latency, cost). The agents dashboard made the cost of the naive approach very concrete:

Agent	Tokens per review	Median latency (local GPU)	Found the cause
raw-baseline (whole report to Gemma)	16K–24K	2.3 s	10/18
second-opinion (parse + rules + Gemma)	1.2K–1.4K	0.7 s	17/18

The dollar cost is $0 either way, because it runs locally. But about 15 times fewer tokens is what lets a 4B-parameter model run on a consultant's laptop at all, instead of needing a 32K context and a bigger GPU.

What I found, and how I debugged it. The first version of that table said 5.0 s for Second Opinion and 6.7 s for the raw baseline. Then I compared it with the traces from running the CLI on the same reports, and the CLI reviews took 0.65–0.92 s:

Same model, same prompt, but a 7× difference in latency. The waterfall put almost all the time in the gen_ai.chat span, so the problem wasn't my code. In the benchmark, the two reviewers alternate, and they asked Ollama for different context sizes (16K for mine, 32K for the raw report). I checked Ollama's load_duration directly. With the same context size, a warm call takes about 0.1 s. Alternating 16K and 32K forces a ~4.2 s model reload on every single call. The benchmark had been timing model reloads, not reviews.

The fix is one line: a single context size for every call. After the fix, the same benchmark in Sentry alternates between 1.9–2.8 s for the raw baseline and 0.64–0.72 s for Second Opinion:

Accuracy didn't change, but every latency figure in this post did, and without the traces I would have published the wrong ones.

Why Does Open Innovation Matter?

For this job, "open" is the product, not a nice-to-have:

The report can't leave the machine. An AWR report is a map of a client's system: SQL text, schema and table names, host names, sometimes bind values. A DBA under NDA can't paste it into a hosted model, however good that model is. An open-weight model running in local Ollama is the only way an LLM gets to touch this work at all.
Statspack shops are the ones without money. The people with no Diagnostics Pack, and so no ADDM, are exactly the people who won't buy another subscription. A free open model on their own hardware is the only version of this they'll ever use.
I could change the model, not just the prompt. With open weights I could fine-tune a small model for this exact task on Tinker, merge the adapter into the model Ollama already had, and run it offline. It agrees with the Tinker-hosted copy on 17 of 18 held-out runs. A closed API would have given me a prompt and nothing else.
Every step is inspectable. The parser, the rules and the number check are about 900 lines of readable Python. A DBA can read why the tool said what it said, which is what they'd demand before putting their name on a client note.

My First User

I didn't want to put a client's report anywhere near this, so the first "user" was a blind reviewer. A separate Claude instance, with no access to my code, labels or results, was told to act as a senior DBA. It was given six lab reports it had never seen, renamed report-1 to report-6, and asked for a by-hand triage: ranked causes, the numbers that convinced it, and what it would check next. I also renamed one sequence whose name gave the answer away. Then I scored its read and Second Opinion's against the injected faults, with the same pass rule as everywhere else:

Report	Injected	Blind reviewer	Second Opinion
AWR	full scan	full scan, CPU cap ✓	full scan, CPU cap, CPU SQL ✓
Statspack	NOCACHE sequence	commits, sequence ✓	commits, sequence ✓
Statspack	healthy	healthy ✓	healthy ✓
AWR	commit storm	commits, small redo logs ✓	commits ✓
Statspack	row locks + literal SQL	hard parse, row locks ✓	hard parse, row locks ✓
AWR	CPU-heavy SQL + literal SQL	commits, CPU SQL ✗	hard parse, commits, CPU cap ✗

What I learned:

5 of 6 each, and both missed the same report, from opposite sides. In the last report, the reviewer found the CPU-heavy regex query but set the literal SQL aside, because hard parsing was only 3.5% of DB time (ADDM agreed). Second Opinion ranked the literal SQL first and never named the regex query, even though it takes a third of DB time. The CPU-SQL rule only fires when CPU is at least half of DB time. Here commit waits and the Resource Manager cap took so much of the time that CPU was only 36%, so the rule never fired. A single statement taking a third of DB time should count whatever else is going on. That gate is my next fix.
My label was wrong, and both of them caught it. That run's literal-SQL workload commits after every row, so there really is a commit storm (log file sync at 42% of DB time). Both reviewers named it. My injected-fault label didn't. Lab labels are what I meant to inject, not always everything the database actually did.
The reviewer added things the tool can't say yet: undersized redo logs (32 log switches in 3 minutes), and that the CPU-cap waits are a symptom of the full scan, not a separate problem. Both are going on the list.

So the "user" is a model playing a DBA, not a paying consultant, and six reports is a small sample. A real DBA's blind pass is the next step.

Prize Categories

Best Use of Tinker: a LoRA fine-tune of Qwen3-8B on 425 lab reports I generated with injected faults, picked on a 61-run validation set and scored once on 18 held-out runs. It went from 11/18 to 16/18, with the top cause right on 18/18 instead of 11/18 and false causes down from 38 to 0, for about $2. Then I exported it to run offline in Ollama, since a model that has to phone home is useless for reports under NDA. The adapter is published on Hugging Face.
Best Use of Sentry Agent Tracing: every review is traced as an agent run (parse → rules → gen_ai.chat), with tokens and latency per call and no report data leaving the machine. The traces showed that the raw-report approach costs about 15 times more tokens. They also caught a benchmark bug: Ollama reloading the model on every call, which had inflated my latencies 7×.

Cutover Went Perfectly. The First New Order Failed With ORA-00001.

Uptime Architect — Sun, 18 Oct 2026 13:32:12 +0000

The migration went by the book. The initial load ran days ahead. On cutover night the final data refresh finished early, row counts matched table for table, the reconciliation report was green, and the application was pointed at the new database. Ten minutes later the first customer placed an order and got an error page:

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:

Initial load, days or weeks before cutover: export and import the whole schema, tables and sequences.
The source keeps running. Customers keep placing orders; ORDER_SEQ keeps advancing on the source.
Final refresh on cutover night: copy just the table data again, so the target catches up. With Data Pump that is CONTENT=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, with CACHE 20 on a release that grows sequence caches dynamically, the source had used 9,000 values but the target sequence started at 22,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, using max(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 no BEHIND.

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 uptimearchitect.com.

You Deleted 99% of the Rows. The Full Scan Didn't Get Any Faster.

Uptime Architect — Wed, 14 Oct 2026 19:45:45 +0000

The audit table had grown to 200 million rows, so the team finally ran the purge: everything older than 90 days, gone. Ninety-nine percent of the rows deleted, committed, celebrated. The next morning the nightly report that full-scans that table ran exactly as long as it always had. Same I/O, same elapsed time, same line in the AWR report. The data was gone. The work wasn't.

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 quick SHRINK 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 with partition 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 by DELETE 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 uptimearchitect.com.

My weather app said the sky would be clear. I taught an open model to check, then went outside

Uptime Architect — Sun, 11 Oct 2026 02:54:00 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 1: Touch Grass

Every stargazer knows this evening. The app says clear. You find the warm jacket, drive out past the streetlights, let your eyes adjust for twenty minutes… and look up at a flat grey lid.

Cloud cover is the one number that decides whether going outside at night is worth it, and it's the number weather apps get wrong most often. I run systems for a living, and when a monitor keeps paging you wrongly you don't stop going outside. You measure how wrong it is, and you correct for it.

So I built Clear Tonight?: it takes the cloud forecast your app shows, looks up 32 months of that same forecast's track record at the same spot, and answers one question with an open model: is the sky really going to be clear tonight, and when should I go?

In short: over a year of nights in 6 cities, it cuts the forecast's error by 28–41%. With only a week of history, TabPFN v2 is the only 12-feature model that beats the app. And on Saturday I committed a forecast, went outside, and the sky was clear.

What I Built

A live page for 6 cities (Seattle, Minneapolis, Chicago, Denver, Austin, London), refreshed every day. For tonight it shows a verdict (Go 21:00–23:00 or Probably not tonight), an hour-by-hour strip of what your app says against the real chance it's clear, and the Moon.
A local version for your own backyard: python -m clear_tonight --lat … --lon … downloads your spot's history, trains on your machine in about 20 seconds, and a local Gemma 4 turns the numbers into a short plan ("…about a 97% chance of really clear skies between 20:00 and 05:00. Since the Moon is 0% lit, it's a great night for faint stars.").
A sky-photo log: point it at a photo you took outside and Gemma 4 rates the cloud cover, so you slowly build your own ground truth.

The screen part takes about five seconds. Everything else is outside.

Demo

Live: clear-tonight.onrender.com: pick a city to see tonight's verdict, the hour-by-hour odds, and the backtest for that city.

An 80-second walkthrough, from the backyard to the backtest:

Code

pyaroslav / clear-tonight

Will the sky really be clear tonight? Your weather app's cloud forecast, corrected by TabPFN v2 from its own track record. Local Gemma 4 for the plan.

Clear Tonight?

Will the sky really be clear tonight? Your weather app's cloud forecast, corrected by an open model that learned from the forecast's own track record at your spot.

Live: https://clear-tonight.onrender.com (6 cities, refreshed daily)

Demo video (80 s): https://youtu.be/Qw1fkWXfr4o

Cloud cover decides whether a night walk, a meteor shower or a look at the stars is worth getting off the couch for, and it's the forecast variable weather apps get wrong most often. Clear Tonight takes the forecast that was issued about 24 hours ahead, compares 32 months of those forecasts against what actually happened at the same place, and lets TabPFN v2 learn the pattern of its mistakes. Then it answers one question for tonight: go or don't go, and when?

Live page (6 cities, refreshed daily): see site/. Deployed as a static site.
Your own spot, fully local: python -m clear_tonight --lat .. --lon ..…

View on GitHub

How I Built It

The question, as data

Open-Meteo keeps an archive of what weather models predicted, including the forecast issued about 24 hours ahead (previous_day1) and 48 hours ahead (previous_day2), and a separate archive of what happened (ERA5 reanalysis). For each city I pulled every night hour (8 PM to 4 AM) from February 2024, when the day-ahead archive begins, to last week: about 975 nights and 8,700 hours per city.

Each hour becomes a row of 12 things you'd know the evening before:

the forecast cloud cover;
humidity and the temperature–dewpoint gap;
wind and precipitation;
what the older forecast said, and how much it changed between runs;
the forecast's view of the whole night;
the hour, and the season.

The label is simple: was the sky actually clear (cloud cover ≤ 25%)?

Why TabPFN

TabPFN is a foundation model for tables. Instead of training a model from scratch, you hand it your rows and it predicts in a single forward pass, having learned on millions of synthetic datasets how small tables usually behave. That's exactly the shape of this problem: one small table per place, no time to tune anything, and the ground truth that matters most, your own sky, starts with a handful of nights.

I used TabPFN v2 deliberately. It's the version released under an Apache-2.0-based license (Prior Labs License 1.1, which adds a "Built with PriorLabs-TabPFN" attribution). The newer versions are non-commercial and need a login. v2 runs fully locally on a GPU, or on a CPU for a table this size.

Honest backtest

Walk-forward over the last 12 months: for every month, train only on what came before it and predict that month, the same way you'd use it live.

City	Brier: weather app	Brier: Clear Tonight	Error cut	Wasted trips (app → us)	Missed clear nights (app → us)
Austin	0.214	0.137	36%	27 → 20	36 → 50
Chicago	0.191	0.130	32%	33 → 19	37 → 53
Denver	0.279	0.164	41%	57 → 35	37 → 48
London	0.194	0.140	28%	16 → 10	47 → 65
Minneapolis	0.233	0.148	36%	29 → 21	51 → 60
Seattle	0.185	0.117	37%	26 → 12	31 → 50

(Brier score: lower is better. A wasted trip is a night that said "go", meaning two clear hours in a row, and turned out cloudy.)

The correction cuts the forecast's probability error by 28–41% in every city, and it roughly halves wasted trips in Seattle and Chicago. The cost is real: it's more cautious, so it misses more clear nights than the app. For a 40-minute drive to a dark-sky park, that's the trade I want. If you'd rather never miss a clear night, use a lower threshold.

And the part I didn't expect: on a full 32 months of history, a plain logistic regression does just as well as TabPFN v2. If you have years of data for one place, you don't need a foundation model.

Where TabPFN actually earns its place

The archive gives any spot on Earth 32 months of history, but its "truth" is ERA5: what happened over a grid cell roughly 30 km wide, not over your backyard or your favourite dark-sky pullout. The truth that really matters is your own sky, and that comes from the photo log one night at a time. So the question that decides which model to use is: what if you only have the last K nights?

Nights of history	TabPFN v2	Logistic regression	Gradient boosting	Recalibrated app number (1 feature)	Weather app as-is
7	0.214	0.357	0.259	0.199	0.237
14	0.204	0.316	0.277	0.183	0.237
30	0.186	0.260	0.248	0.179	0.237
60	0.176	0.199	0.253	0.179	0.237
120	0.171	0.161	0.237	0.176	0.237
365	0.153	0.149	0.168	0.174	0.237

(6 cities × 6 months, Brier score.)

With a week of data, the classic models given the same 12 features are worse than not correcting at all; they overfit. TabPFN v2, with the same 12 features, beats the raw forecast from the very first week, is the best model of all at two months, and is only overtaken by logistic regression after about four months.

In fairness: for the first month, the simplest fix of all wins, which is just recalibrating the app's own cloud number (0.199 at 7 nights). So the honest recipe is: recalibrate on day one, and switch to TabPFN v2 at around two months. The app uses TabPFN from the start, because it's never worse than the raw forecast and it's the only model that gets better as you add the other 11 features.

Gemma 4: words, not numbers

The local version asks Gemma 4 (gemma4:e4b via Ollama) to turn the hour-by-hour odds into a sentence. In my first try, Gemma read the table and told me the worst two hours were the best, and that the app was more pessimistic than my correction, when it was the other way round. Lesson re-learned: code computes the facts (best window, which forecast is more optimistic, Moon phase) and Gemma only words them. Gemma also does one thing code can't: it looks at a phone photo of the sky and estimates the cloud cover, which becomes a ground-truth log. On the night it rated 3 of 4 photos correctly, and the fourth taught me something (below).

Plumbing

A GitHub Action runs every afternoon. It trains TabPFN v2 on a CPU runner (a single ensemble member there; the backtest used the default four on a GPU), writes tonight.json, and commits it. Render serves site/ as a static site, and the page reads the latest tonight.json straight from the repo, so it's fresh without a redeploy. There's no backend and no API key, and the whole thing costs nothing to run.

Taking it outside

On Saturday morning, Oct 10, I ran it for Minneapolis and committed the forecast to the repo before going anywhere, so I couldn't quietly edit it afterwards. It was about as confident as it gets: 96–98% chance of a clear sky every hour from 8 PM to 5 AM, and a new Moon. Gemma's plan: "Go outside tonight for stargazing… Since the Moon is 0% lit, it's a great night for faint stars. Remember to wear warm layers."

(The warm layers were wrong. It was 80 °F in October.)

At about 7:50 PM I went out to the backyard. The sky was clear from edge to edge, and the first stars were already out before full darkness. It was pleasantly warm, just right for relaxing; I barely moved. The crickets were calming, and the neighbourhood was fantastic.

The forecast was right, and the weather model's own 15-minute record for the evening agrees: 0% cloud from 5:45 to 8 PM. One clear night under a confident forecast proves little; the backtest above is the real evidence. But it was worth going out, and that's the point of the app.

Gemma rated 3 of 4 photos correctly, and the fourth taught me something. I ran my sky photos through the photo log. Three came back right: 5–10% cloud, clear sky. The fourth was the same clear sky a few seconds later, but the phone's night mode had lifted the twilight and city glow into a flat grey. Gemma called it 95% cloud, no stars, high confidence, and said the same thing on three re-runs.

That's a useful thing to learn before building on the photo log. A small-data model trained on your own sky is only as good as its labels, and one confident wrong label is worth a lot in a table of 30 rows.

So I fixed it that evening. The photo log now checks every rating against the other photos taken within 15 minutes and against the weather model's cloud cover for that hour. If a rating disagrees with both by 50 points or more, it's held back as needs review instead of being trusted. Re-running the same four photos:

sky 1     0% cloud, stars visible (high)     check: ok
sky 2    10% cloud, stars visible (medium)   check: ok
sky 3    95% cloud, no stars (high)          check: needs review: other photos ~5%, weather model 0%
backyard 10% cloud, no stars (medium)        check: ok

It's the same lesson as the narration: let the model describe what it sees, but check it against what you already know.

I also committed a forecast for the night before, Oct 9, and didn't go out. It will be scored against the final ERA5 data when that's published around Oct 14–15. The early estimates for that night disagree with each other, so I'm not calling it either way yet. I'll post the result in the comments.

Limits, and what's next

"Truth" is ERA5, a reconstruction over a grid cell about 30 km wide, not a person looking up from your backyard. That's why the photo log exists, but it doesn't train the model yet. Next: once a spot has a few dozen checked photos, fit TabPFN on those. That's exactly the small-table case the learning curve says it's good at.
One forecast model. The input is GFS's day-ahead forecast, because that's what the archive keeps for every day since February 2024. Other models might need less correcting.
"Clear" means 25% cloud or less, per hour. Serious astrophotographers would want a stricter line, and casual stargazers a looser one. It's one constant in the code.
Cautious by design. It misses more clear nights than the app does, in exchange for fewer wasted trips.
One night outside proves nothing on its own. The evidence is the 12-month backtest across 6 cities. The walk is the part a backtest can't show: was it worth going out? (Yes.)

Why Does Open Innovation Matter?

The forecast's track record is the product, and it's open too. The whole project rests on Open-Meteo publishing not just today's forecast but every past forecast next to what really happened. Closed weather APIs give you a prediction; open data lets you audit it.
An open tabular foundation model fits a one-backyard problem. TabPFN v2's weights run on my desktop's GPU (or a free CI runner) with no account and no per-call cost. That's what makes "train on your spot tonight" a 20-second command instead of a product decision.
Your location stays yours. The local version sends nothing but coordinates to the weather archive. The model, the plan and your sky photos stay on your machine.
I could check the claims myself. Because everything is open, I could measure exactly where TabPFN helps (little data) and where it doesn't (lots of data), and say so. A hosted black box would have given me a nice number and no idea whether to trust it.

Prize Categories

Best Use of TabPFN: TabPFN v2 (Apache-2.0-based license) is the model. It's backtested walk-forward against the raw forecast, climatology, logistic regression and gradient boosting across 6 cities, with a learning curve showing where it wins.
Best Use of Render: the live page is a free Render static site serving the repo's site/ folder (a render.yaml Blueprint is included). It shows the forecast the GitHub Action makes with TabPFN v2 every day.
Best Use of Gemma: Gemma 4 runs locally to turn the forecast into a plan and to rate sky photos for ground truth, plus a cross-check I added on the night that catches the kind of photo it can misread.

Built with PriorLabs-TabPFN. Weather data by Open-Meteo.com (CC BY 4.0).

— Yaro

The Migration Completed Successfully. Half the Names Are Now Question Marks.

Uptime Architect — Wed, 07 Oct 2026 11:08:58 +0000

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é becomes Jose, 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, because Jose 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 of zero. 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 wrong NLS_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. A VARCHAR2(10 BYTE) column that held a 10-character name may no longer fit — the import fails with ORA-12899, or worse, silently truncates. Define columns with CHAR 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 wrong client. 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. Set NLS_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 as Jose, 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.

Frequently asked questions

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 uptimearchitect.com.

Your Oracle Traffic Is Readable on the Wire. Here's the Proof, and the Fix.

Uptime Architect — Sun, 04 Oct 2026 18:26:04 +0000

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. Use REQUIRED 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. Check V$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.

Frequently asked questions

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 uptimearchitect.com.