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

Описание и применение

Ghost Hunt обнаружил и устранил «привидения» в Oracle, включая зависшие распределённые транзакции, и восстановил доступ к заблокированным строкам.

Задача

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.

Результат

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

Рабочий процесс

• 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.

Доказательства

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

Источник

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

Источник

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

Источник