Oracle 資料列被鎖、鎖定它的工作階段已消失：我打造了幽靈獵手

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:

1. the row nobody can read;
2. the hunt and its report;
3. I kill -9 the worker while the hunt waits for me;
4. I approve the exorcism anyway, while no worker is running;
5. 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

…

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:

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.