Redefine a Table Online with DBMS_REDEFINITION, No DML Blocking

calendar Sep 18, 2026 / · 11 min read · Oracle DBA DBMS_REDEFINITION Online Table Redefinition Table Reorganization START_REDEF_TABLE FINISH_REDEF_TABLE Materialized View Logs Oracle Administration Table Compression  ·
Share on: linkedin copy

Redefine a Table Online with DBMS_REDEFINITION, No DML Blocking

Purpose

A DBA moving a table to a new tablespace, adding compression, or rebuilding a badly fragmented segment has one hard requirement: users can't lose access to that table while the change is happening. For decades the available tools — CREATE TABLE AS SELECT plus a rename, or a plain ALTER TABLE MOVE — all lock the table for at least part of the operation, even if only briefly. DBMS_REDEFINITION restructures a table while it stays fully readable and writable the entire time.

The mechanism behind that is not magic — it's materialized view logs. Oracle builds incrementally maintainable local materialized views against the original table, and those logs track every change made to it while the redefinition is in progress. When the interim table is synchronized, Oracle replays the tracked changes into it, which is what lets an application keep issuing DML against the original table for the whole window instead of freezing at the start. Subprograms in the package run with invokers' rights, and Oracle splits access into two modes: USER mode, where a user redefines a table in their own schema, and FULL mode, where a user holding the right ANY privileges can redefine a table in any schema.

This post covers the full multi-step workflow — checking eligibility, starting the redefinition, cloning dependent objects, synchronizing, and finishing or rolling back — plus the single-step REDEF_TABLE procedure that folds several of those steps into one call for straightforward storage changes. It also covers the privilege split between USER and FULL mode in more detail, and a data-dictionary side effect of the redefinition process that catches DBAs who haven't seen it before: what happens to audit policies once the operation completes.

Code

 1-- Step 1: original and interim tables, same structure, different tablespaces
 2CREATE TABLE emp (
 3  empno    NUMBER(4,0) PRIMARY KEY,
 4  ename    VARCHAR2(10),
 5  job      VARCHAR2(9),
 6  mgr      NUMBER(4,0),
 7  hiredate DATE,
 8  sal      NUMBER(7,2),
 9  comm     NUMBER(7,2),
10  deptno   NUMBER(2,0)
11) TABLESPACE myts;
12
13CREATE TABLE emp_int (
14  empno    NUMBER(4,0) PRIMARY KEY,
15  ename    VARCHAR2(10),
16  job      VARCHAR2(9),
17  mgr      NUMBER(4,0),
18  hiredate DATE,
19  sal      NUMBER(7,2),
20  comm     NUMBER(7,2),
21  deptno   NUMBER(2,0)
22) TABLESPACE compressed_ts;
23
24-- Step 2: confirm the table is a valid redefinition candidate before starting
25EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EMP', DBMS_REDEFINITION.CONS_USE_ROWID);
26
27-- Step 3: start the redefinition, rollback kept available
28BEGIN
29  DBMS_REDEFINITION.START_REDEF_TABLE(
30    uname           => 'SCOTT',
31    orig_table      => 'EMP',
32    int_table       => 'EMP_INT',
33    enable_rollback => TRUE);
34END;
35/
36
37-- Step 4: clone grants, triggers, constraints, and indexes onto the interim table
38DECLARE
39  num_errors PLS_INTEGER;
40BEGIN
41  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
42    uname            => 'SCOTT',
43    orig_table       => 'EMP',
44    int_table        => 'EMP_INT',
45    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
46    copy_triggers    => TRUE,
47    copy_constraints => TRUE,
48    copy_privileges  => TRUE,
49    num_errors       => num_errors);
50  DBMS_OUTPUT.PUT_LINE('Errors: ' || num_errors);
51END;
52/
53
54-- Step 5: keep the interim table in sync -- run repeatedly while evaluating (e.g. hourly)
55EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_INT');
56
57-- Step 6a: satisfied with the result -- commit the redefinition
58EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP', 'EMP_INT');
59
60-- Step 6b: not satisfied -- roll back to the original table instead
61EXEC DBMS_REDEFINITION.ROLLBACK('SCOTT', 'EMP', 'EMP_INT');
62
63-- Step 7: once no rollback will be needed, close that door and free the tracking objects
64EXEC DBMS_REDEFINITION.ABORT_ROLLBACK('SCOTT', 'EMP', 'EMP_INT');
65
66-- Single-step alternative: REDEF_TABLE folds the workflow above into one call
67-- for a straightforward storage change -- tablespace, table compression,
68-- index compression, and LOB storage/compression together
69BEGIN
70  DBMS_REDEFINITION.REDEF_TABLE(
71    uname                      => 'PM',
72    tname                      => 'PRINT_ADS',
73    table_compression_type     => 'ROW STORE COMPRESS ADVANCED',
74    table_part_tablespace      => 'USERS',
75    index_key_compression_type => 'COMPRESS 1',
76    index_tablespace           => 'USERS',
77    lob_compression_type       => 'COMPRESS HIGH',
78    lob_tablespace             => 'USERS',
79    lob_store_as                => 'SECUREFILE');
80END;
81/

Code Breakdown

Step 2: CAN_REDEF_TABLE

This is the mandatory first call. It checks whether the target table qualifies for online redefinition and raises an error if it doesn't, before anything else has been touched. The options_flag parameter selects the redefinition method: DBMS_REDEFINITION.CONS_USE_PK (the default) redefines using the primary key or a pseudo-primary key — a unique key whose columns are all NOT NULL; DBMS_REDEFINITION.CONS_USE_ROWID redefines using rowids instead, which is the option a table with no primary key and no qualifying unique key has to use.

Step 3: START_REDEF_TABLE

This begins the actual redefinition process against a manually created, empty interim table in the same schema as the original. From this point until FINISH_REDEF_TABLE or ABORT_REDEF_TABLE is called, both tables exist and the materialized-view-log mechanism is actively tracking changes to the original. Setting enable_rollback => TRUE keeps the option open to undo everything later with the ROLLBACK procedure.

Step 4: COPY_TABLE_DEPENDENTS

The interim table starts out as a structural copy only — it does not automatically carry the original's grants, triggers, constraints, or indexes. COPY_TABLE_DEPENDENTS clones those across and registers them as dependent objects of the redefinition. copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS tells it to clone indexes using the same storage parameters the originals used, rather than defaults. The num_errors output parameter reports how many of the requested clones failed, so a script can check it rather than assuming success.

Step 5: SYNC_INTERIM_TABLE

Because the original table stays fully live during the whole process, the interim table drifts out of sync with every DML statement that runs against the original after START_REDEF_TABLE. SYNC_INTERIM_TABLE applies the tracked changes and brings it current again. Calling this repeatedly — say, once an hour — while evaluating a change keeps the eventual FINISH_REDEF_TABLE call fast, since less catching-up remains to do at cutover.

Step 6: FINISH_REDEF_TABLE, ROLLBACK, and ABORT_ROLLBACK

FINISH_REDEF_TABLE performs the actual swap: a final synchronization runs, then the original and interim tables exchange identities, with the interim table's structure taking over the original table's name. ROLLBACK is the opposite path — available only when enable_rollback was set to TRUE at START_REDEF_TABLE — and restores the original table to its pre-redefinition state. ABORT_ROLLBACK doesn't roll anything back itself; it terminates the possibility of a later rollback, releasing the objects that made it available once the DBA is confident the change should stick.

Single-step REDEF_TABLE

REDEF_TABLE is a push-button alternative that runs several of the steps above internally and is built for a specific case: changing a table's storage properties — tablespace, compression type, index compression, LOB storage — without needing to hand-manage an interim table, dependent-object cloning, or a sync loop. It still supports enable_rollback, but the reference documentation is explicit that rollback is not supported when REDEF_TABLE itself is the procedure used to perform the redefinition — a distinction worth checking before assuming the flag behaves identically to the multi-step path.

Key Points

  • The mechanism is materialized view logs, not a lock. Oracle tracks changes to the original table through incrementally maintainable materialized views, which is what lets DML continue against it throughout the process.
  • CAN_REDEF_TABLE is not optional. It's the documented first step, and it raises an error outright if the table isn't a valid candidate rather than letting a later step fail unpredictably.
  • The redefinition key defaults to primary key, not rowid. CONS_USE_PK (primary key or a NOT-NULL unique key) is the default method; CONS_USE_ROWID is the explicit fallback for a table without one.
  • REDEF_TABLE does not support rollback, even though the parameter exists on the call — only the multi-step START_REDEF_TABLE / ROLLBACK path does.
  • Object IDs swap at FINISH_REDEF_TABLE, not names. The interim table takes on the original table's object ID during the swap — which has a direct consequence for anything keyed to that ID (see below).
  • Two privilege modes govern who can redefine what. USER mode covers a user redefining their own schema's table; FULL mode, requiring the ANY-level privileges, covers redefining a table in someone else's schema.

Insights and Best Practices

Recheck audit policies after a redefinition completes

Because the interim table takes on the original table's object ID during the swap, and Oracle audit policies are tied to object IDs rather than object names, any audit policy that existed on a table before it was redefined keeps auditing "the same" object afterward — which is now, physically, the former interim table. This is easy to overlook because nothing about the table's name or apparent identity changed from the application's point of view. Checking existing audit policies against a table right after redefining it, and adjusting them if the intent was actually to audit a specific physical object rather than a logical name, closes that gap before it becomes a compliance surprise.

Match privileges to the mode you actually need

Redefining a table in your own schema (USER mode) needs execute privilege on the DBMS_REDEFINITION package plus CREATE TABLE and CREATE MATERIALIZED VIEW; the COPY_TABLE_DEPENDENTS step additionally needs CREATE TRIGGER if the table has triggers to clone. Redefining a table in another schema (FULL mode) needs a wider set — CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, and SELECT ANY TABLE, plus CREATE ANY TRIGGER and CREATE ANY INDEX if COPY_TABLE_DEPENDENTS is run against a table outside the connecting user's own schema. Granting the FULL-mode privilege set to an account that only ever needs USER mode is broader access than the job requires.

Use the sync window to actually evaluate the change

The gap between START_REDEF_TABLE and FINISH_REDEF_TABLE isn't just plumbing — it's a live evaluation window. A table moved to a different tablespace, or given a different compression setting, can be checked for real query performance during that window with SYNC_INTERIM_TABLE calls keeping it current, and the decision to commit or roll back can be made from actual observed behavior rather than a guess made in advance.

Reach for REDEF_TABLE only when rollback isn't a requirement

Because REDEF_TABLE doesn't support rolling back, it fits a narrower case than the full multi-step workflow: a storage-property change the DBA is already confident about, where the value of a one-call interface outweighs the value of a safety net. For a change carrying more uncertainty — a first attempt at a new compression setting on a business-critical table, for instance — the multi-step path with enable_rollback => TRUE is the more conservative choice.

Drop the interim table once FINISH_REDEF_TABLE has run

After a successful FINISH_REDEF_TABLE, the table that started as the interim table has taken over the original name, and the name that was passed in as int_table now refers to what's left of the old structure. That leftover object still occupies space and should be dropped once it's no longer needed, rather than left sitting in the schema as an orphaned artifact of the process.

When to Use This

  • Reclaiming space or reducing fragmentation on a large table without a maintenance window.
  • Moving a table to a different tablespace while the application keeps reading and writing it.
  • Adding or changing table, index, or LOB compression on a table that already holds production data.
  • Evaluating a storage change against real query load before committing to it, with the option to roll back if it doesn't help.
  • Standardizing storage settings — tablespace placement, compression type — across a table where ALTER TABLE MOVE's DML restriction isn't acceptable.

Troubleshooting Common Issues

CAN_REDEF_TABLE raises an error. The table isn't currently a valid redefinition candidate. If the call used the default CONS_USE_PK method, confirm the table actually has a primary key or a qualifying NOT-NULL unique key — if it doesn't, CONS_USE_ROWID is the alternative to pass instead.

A privilege error appears partway through the workflow. Confirm the mode: redefining a table in your own schema needs CREATE TABLE and CREATE MATERIALIZED VIEW at minimum (plus CREATE TRIGGER if COPY_TABLE_DEPENDENTS needs to clone triggers); redefining a table in another schema needs the broader FULL-mode privilege set, including CREATE ANY TABLE and ALTER ANY TABLE.

ROLLBACK fails or isn't available after using REDEF_TABLE. This is expected — online redefinition rollback is not supported when REDEF_TABLE was the procedure used to perform the redefinition. Only the multi-step START_REDEF_TABLE path, with enable_rollback => TRUE, supports a later ROLLBACK call.

An audit policy on a table stops matching what's expected after a redefinition. Check the table's object ID — the redefinition swapped it with the interim table's ID at FINISH_REDEF_TABLE, and audit policies key on object ID rather than object name.

References

Posts in this series