2026-08-31 · run 2026-08-31-oracle-index-audit-port

Oracle index-audit port

What was done

Ported bin/index-audit.sh / bin/index-audit.py to Oracle, closing the last engine for the schema-correctness gate.

The port

• index-audit.py
  • norm() now strips all double-quotes (Oracle writes expressions with quoted uppercase identifiers: SUBSTR("VALUE",1,250) → substr(value,1,250)) and folds the result to the changelog's <modifySql dbms="oracle"> form.
  • col_norm()'s (N) prefix-length strip is now scoped to mysql/mariadb only (Oracle has no prefix-length columns; previously != postgresql also applied it to Oracle).
  • Added parse_db_oracle() (index-name → ordered column list) and the --engine oracle choice.
• index-audit.sh
  • The Oracle catalogue query reads USER_IND_COLUMNS joined to USER_IND_EXPRESSIONS. USER_IND_EXPRESSIONS.COLUMN_EXPRESSION is a LONG, which cannot be concatenated or SUBSTRed in a plain SELECT (ORA-00932/ORA-24856), so the emission is a PL/SQL block that reads the expression into a VARCHAR2(4000) and writes index|column lines via DBMS_OUTPUT. BIN$% recycle-bin ghosts are excluded.

Result

--- 121 index definitions applied to this database: 121 correct, 0 missing, 0 wrong shape, 0 unverified (9 skipped: table dropped in a later version)

The 26.7.3 Oracle schema is correct by definition — including the function-based indexes (IDX_GROUP_ATT_BY_NAME_VALUE → SUBSTR("VALUE",1,250)), which is the Oracle spelling of the same index that produced the postgres false-positive earlier.

Remaining Oracle tooling

• snapshot/restore — still needs Data Pump (expdp/impdp); the one Oracle item left unported.

Verification