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

Description and use

Ghost Hunt identified and resolved ghost objects in Oracle, including an in‑doubt distributed transaction, restoring access to locked rows.

Problem

Oracle left a row locked by an in‑doubt distributed transaction after the session that promised to commit disappeared, preventing any session from reading the row.

Outcome

Ghost Hunt successfully identified and resolved an in‑doubt distributed transaction and other ghost objects, restoring normal database operation and preventing resource leaks.

Workflow

• Connect to each PDB as c##ghost with limited privileges.
• Run a dedicated SQL query per ghost type to list ghosts.
• Gemma writes a report with two‑line header and epitaph per ghost.
• Temporal workflow waits up to 12 hours for human approval of each exorcism.
• On approval, execute the appropriate DDL/DML (e.g., commit force, purge_lost_db_entry).
• After action, Ghost Hunt re‑runs to confirm ghosts are gone.
• Repeat until no ghosts remain.

Evidence

Oracle commits across two databases in two steps: both sides promise, then both commit. This one died between the steps.

Source

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

Source

Gemma writes the field notes: two lines on top, and an epitaph for each ghost.

Source