clear_all_wolverine_storage on Oracle: unreachable failure handler, and the listener that makes ORA-00054 reachable at all

[jeremydmiller]

Follow-up to #3808, which fixed the schema the cleanup targeted but did not finish the job. Main run 30877953106 shows the class still costing one retry, now with a different error:

OracleTests.Transport.clear_all_wolverine_storage.is_idempotent
  Oracle.ManagedDataAccess.Client.OracleException : ORA-00054: Failed to acquire a lock (Type: "TM", Name: "DML"...)

Two separate problems.

1. The failure handler is unreachable (my defect, introduced in #3808)

catch (OracleException e) when (e.Number == 54 && attempt < maxAttempts)
{
    await Task.Delay(TimeSpan.FromSeconds(2 * attempt));
}
// ...
throw new InvalidOperationException($"Could not drop {TransportSchemaName}.{table} after ...");

On the final attempt the when guard is false, so the raw OracleException propagates out of the loop and the descriptive throw below is dead code. It was written specifically so the failure would name the lock and the leftover rows, and it has never once run — which is exactly what the CI error above demonstrates.

Fix: catch 54 unconditionally, and rethrow with context after the loop.

2. ORA-00054 is only reachable because this class attaches a listener

Before #3808 the DROP always hit ORA-00942 against a table in the wrong schema, so it never contended with anything. Now that it resolves a real table it contends with a listener holding a TM/DML lock — and DDL_LOCK_TIMEOUT = 30 plus ~20s of backoff was not enough on a CI runner.

The compliance base class is explicit about why this class is different:

Register it as a subscriber only — never attach a listener. Throttling a listener's polling interval is not enough: several of these transports poll once on startup regardless.

Every other provider in ClearAllWolverineStorageCompliance (Postgres, SqlServer, MySql, Sqlite) is subscriber-only. Oracle is the sole deviation, and the file records the reason:

Oracle uppercases queue identifiers, but Uri.Host lowercases, so the publishing.To(queue.Uri) inside ToOracleQueue() resolves a second endpoint over the same physical tables.

That casing mismatch is the actual bug. It is worth checking whether it affects production code and not just this test — an endpoint resolved twice over one set of tables is not obviously test-only. Fix it and the class can go subscriber-only like its siblings, and the lock contention disappears rather than being retried around.

Priority

Low-moderate. It is one retry and has never turned main red. Item 1 is a five-minute fix and should just be done; item 2 is the one with potential product impact behind it.