oracle-migration Skill Evaluation Report¶

Method: skill-creator A/B testing Date: 2026-04-18 Subject: skills/oracle-migration/ — Oracle Database schema migration safety reviewer and DDL generator

The oracle-migration skill covers DDL auto-commit semantics, DDL_LOCK_TIMEOUT configuration, large-table online redefinition via DBMS_REDEFINITION, the ENABLE NOVALIDATE constraint pattern, global index invalidation prevention, and the mandatory §9 output contract. The evaluation ran three A/B scenarios (six real model calls) scoring 23 assertions against both with-skill and without-skill responses, with actual token usage recorded for each run.

The most important finding cuts against the expected narrative: the baseline model already handles common Oracle safety patterns — FK constraint risks, ORA-02298 orphan rows, and ENABLE NOVALIDATE two-step — with solid accuracy. The skill's differentiated value is concentrated in three areas: enforcing DDL_LOCK_TIMEOUT (missed in 3/3 baseline runs), correctly classifying NUMBER precision widening as a metadata operation rather than a table rewrite, and guaranteeing a structured scorecard, rollback plan, and uncovered-risk section regardless of time pressure or incomplete context.

1. Skill Overview¶

Core files:

Key safety rules the skill enforces:

• Set ALTER SESSION SET DDL_LOCK_TIMEOUT = N before every DDL statement (Critical; baseline omits this in all three scenarios)
• Classify column modifications against the DDL lock matrix before recommending a migration path — widening NUMBER precision is a metadata operation, not a table rewrite
• Use ENABLE NOVALIDATE + VALIDATE two-step for adding NOT NULL or FK constraints on populated tables
• Recommend CREATE INDEX ... ONLINE with an explicit Enterprise Edition prerequisite note
• Produce a §9 output contract on every run: context collection table (§9.1), per-DDL risk scorecard (§9.8), uncovered-risk section (§9.9), and rollback SQL
• Formally declare a degraded mode level (MINIMAL / Degraded) when required context is absent, and list blocking unknowns in §9.9 rather than issuing conditional advice

2. Test Design¶

2.1 Scenarios¶

2.2 Assertion Matrix (23 total)¶

Scenario 0 — Standard DDL review (9 assertions)

Scenario 0 result: Without Skill 3.5/9 weighted (1 PASS + 5 PARTIAL + 3 FAIL) | With Skill 9/9

Token usage: Without Skill 14,195 tokens (0 tool calls) | With Skill 41,080 tokens (9 tool calls, including SKILL.md + two reference files)

Scenario 1 — Large-table column type change (8 assertions)

Scenario 1 result: Without Skill 4/8 weighted (3 PASS + 2 PARTIAL + 3 FAIL) | With Skill 8/8

Token usage: Without Skill 16,288 tokens (0 tool calls) | With Skill 41,362 tokens (9 tool calls, all three reference files loaded)

Scenario 2 — Degraded mode, no context (6 assertions)

Scenario 2 result: Without Skill 4/6 weighted (4 PASS + 0 PARTIAL + 2 FAIL) | With Skill 6/6

Token usage: Without Skill 13,266 tokens (0 tool calls) | With Skill 33,754 tokens (7 tool calls, SKILL.md + oracle-ddl-lock-matrix.md)

3. Results¶

3.1 Overall¶

Delta: +50 percentage points (weighted)

Weighted formula: PASS = 1.0, PARTIAL = 0.5, FAIL = 0. Weighted score = (PASS + PARTIAL × 0.5) / 23.

3.2 By scenario¶

3.3 Where the skill makes a difference¶

3.4 Where the baseline is already strong¶

The baseline model handles these Oracle patterns correctly without the skill:

• ORA-02296 (NOT NULL enforcement when existing NULLs are present) — correctly identified in S0
• ORA-02298 (FK VALIDATE encountering orphan rows) — correctly identified in S2
• ENABLE NOVALIDATE + VALIDATE two-step pattern — full SQL provided in S2
• FK index absence → parent-table DML lock contention and deadlock risk — correctly identified in S2 including DELETE scenarios
• DBMS_REDEFINITION overall workflow (CAN_REDEF → START → SYNC → FINISH → ABORT) — complete steps in S1
• UNDO/TEMP tablespace assessment — correctly raised in S1

The skill's core value is not filling an Oracle knowledge gap — the baseline is technically solid on common patterns. It is enforcing DDL_LOCK_TIMEOUT (a consistently missed Oracle-specific safety parameter), providing accurate DDL behavior classification for edge cases, and guaranteeing structural output completeness under time pressure or incomplete context.

4. Token Cost Analysis¶

Token counts are full session totals (input + tool calls + tool results + output) measured from the Agent tool's usage field.

The +166% average overhead is higher than the mysql-migration evaluation (+51%) for two reasons. First, this evaluation used the Agent tool for file reads, which carries per-call overhead. Second, oracle-migration's reference files are denser — the large-table migration guide is 303 lines of PL/SQL workflow. In production use, where SKILL.md content is injected as a system prompt rather than loaded via tool calls, actual overhead would drop to roughly SKILL.md (~8,000 tokens) plus on-demand reference files, bringing it closer to the mysql-migration range.

5. Coverage Gaps¶

6. Conclusion¶

oracle-migration achieved 100% assertion coverage across six real model runs and 23 scored assertions (weighted pass rate up from 50% to 100%, +50 pp).

The most significant finding is the baseline's strength: the base model scores 67% weighted on the hardest scenario (degraded mode with no context), and correctly handles ORA-02298, ENABLE NOVALIDATE, and FK deadlock risks without any skill guidance. This shifts the interpretation of the skill's value — it is not a knowledge delivery vehicle, but a structural and safety enforcement layer.

Four contributions where the skill delivers measurable value:

1. DDL_LOCK_TIMEOUT enforcement — The baseline omitted this parameter in all three scenarios. On a live production database, a DDL statement without a timeout will fail immediately with ORA-00054 if any long-running transaction holds a conflicting lock. The skill marks this as a Critical check via §5.1 and AE-1, making it impossible to miss.

2. Accurate DDL behavior classification — S1 showed the baseline recommending a full DBMS_REDEFINITION workflow for a NUMBER(10→18) widening. The skill's DDL lock matrix correctly identifies this as a metadata-only MODIFY column (widen) operation, producing a simpler, faster, and equally safe migration path.

3. Structural output contract — The §9 output contract guarantees a scorecard, rollback SQL, and §9.9 uncovered-risk section on every run. Without the skill, none of these appeared in any baseline response. The scorecard format (X/12 — Critical Y/3 — PASS/FAIL) is directly usable as a deployment gate.

4. Formal degraded mode — When context is incomplete, the skill assigns a named degraded level (MINIMAL) and converts unknowns into structured blocking gaps in §9.9. The baseline gives useful conditional advice but without a formal framework that prevents an overconfident judgment from slipping through.

Recommendation: production-ready. Recommended for all Oracle DDL review workflows, with the highest single-item value in no-maintenance-window production environments where DDL_LOCK_TIMEOUT enforcement is the difference between a clean migration and an ORA-00054 failure.