DBMS_REDEFINITION - Online Table Reorganization in Oracle

DBMS_REDEFINITION enables DBAs to reorganize Oracle tables while they remain online and fully available for DML. Applications continue reading and writing the table throughout the process. The package copies data to an interim table, applies incremental changes captured during the copy phase, then swaps the tables in a brief lock window — typically under a second.

Overview

When to Use DBMS_REDEFINITION

Use Case	Example
Move to a different tablespace	Migrate a table from `USERS` to `APP_DATA`
Add or change partitioning	Convert a heap table to a range-partitioned table
Change compression	Enable `ROW STORE COMPRESS ADVANCED` without downtime
Add or remove columns	Reorder columns or populate a new column during the copy
Change storage parameters	Adjust `PCTFREE`, `INITRANS`, row chaining
Convert to or from IOT	Convert a regular table to an Index Organized Table
Reclaim fragmented space	Rebuild a heavily deleted/updated table without shrinking

How It Works

1. CAN_REDEF_TABLE   → Validate the table can be redefined
2. START_REDEF_TABLE → Create interim table, begin copying rows
3. COPY_TABLE_DEPENDENTS (optional) → Clone indexes, triggers, grants to interim
4. SYNC_INTERIM_TABLE (optional, repeat) → Catch up with DML during long copies
5. FINISH_REDEF_TABLE → Final sync + atomic table swap
6. Drop interim table + clean up original dependents

During steps 2–5, the original table remains fully accessible. The lock window is only the final atomic swap in step 5.

Prerequisites Check

-- Required privileges
GRANT EXECUTE ON DBMS_REDEFINITION TO dba_user;

-- The user performing redefinition needs:
-- CREATE TABLE, ALTER ANY TABLE, DROP ANY TABLE,
-- LOCK ANY TABLE, SELECT ANY TABLE in the target schema
-- (or ownership of the table)

-- Check if the table is eligible for redefinition
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'HR',
    tname        => 'EMPLOYEES',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
  DBMS_OUTPUT.PUT_LINE('Table can be redefined.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Cannot redefine: ' || SQLERRM);
END;
/

-- options_flag values:
-- DBMS_REDEFINITION.CONS_USE_PK   (1) - use primary key for row matching (preferred)
-- DBMS_REDEFINITION.CONS_USE_ROWID (2) - use ROWID (use when no PK exists)

CAN_REDEF_TABLE

Validates that the table meets all requirements before starting. Always call this first.

-- Tables that CANNOT be redefined:
-- Tables with materialized view logs as master
-- Temporary tables
-- Clustered tables
-- Object tables with REF columns pointing outside the table
-- Tables with LONG or LONG RAW columns (convert to LOB first)
-- Tables with horizontal partitioning across multiple schemas

-- Validate with error detail
DECLARE
  l_err_code  NUMBER;
  l_err_msg   VARCHAR2(4000);
BEGIN
  BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('HR', 'ORDERS',
      DBMS_REDEFINITION.CONS_USE_PK);
    DBMS_OUTPUT.PUT_LINE('OK: Table eligible for online redefinition.');
  EXCEPTION
    WHEN OTHERS THEN
      l_err_code := SQLCODE;
      l_err_msg  := SQLERRM;
      DBMS_OUTPUT.PUT_LINE('NOT ELIGIBLE: ' || l_err_msg);
  END;
END;
/

START_REDEF_TABLE

Starts the redefinition. Oracle copies existing rows to the interim table and begins logging changes to the original.

-- Syntax
DBMS_REDEFINITION.START_REDEF_TABLE(
  uname              IN VARCHAR2,    -- schema
  orig_table         IN VARCHAR2,    -- original table name
  int_table          IN VARCHAR2,    -- interim table name (must already exist)
  col_mapping        IN VARCHAR2 := NULL,  -- column mapping expression
  options_flag       IN BINARY_INTEGER := 1,
  orderby_cols       IN VARCHAR2 := NULL,
  part_name          IN VARCHAR2 := NULL   -- for single partition redefinition
);

Use Case 1: Move Table to a Different Tablespace

-- Step 1: Create the interim table in the new tablespace
-- (No data — DBMS_REDEFINITION populates it)
CREATE TABLE hr.employees_interim
  TABLESPACE app_data
  STORAGE (INITIAL 10M NEXT 10M)
AS
  SELECT * FROM hr.employees WHERE 1=0;  -- structure only

-- Step 2: Validate
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('HR', 'EMPLOYEES',
    DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- Step 3: Start redefinition (copies rows from EMPLOYEES to EMPLOYEES_INTERIM)
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'HR',
    orig_table   => 'EMPLOYEES',
    int_table    => 'EMPLOYEES_INTERIM',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

-- Step 4: Copy dependents (indexes, triggers, constraints, grants)
DECLARE
  l_num_errors NUMBER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'HR',
    orig_table       => 'EMPLOYEES',
    int_table        => 'EMPLOYEES_INTERIM',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_num_errors
  );
  DBMS_OUTPUT.PUT_LINE('Errors during copy: ' || l_num_errors);
END;
/

-- Step 5: (Optional) Sync interim table to catch up with recent DML
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'EMPLOYEES', 'EMPLOYEES_INTERIM');
END;
/

-- Step 6: Finish — final sync + atomic table swap
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'EMPLOYEES', 'EMPLOYEES_INTERIM');
END;
/

-- Step 7: Drop the old table (now named EMPLOYEES_INTERIM after the swap)
DROP TABLE hr.employees_interim;

-- Verify new tablespace
SELECT table_name, tablespace_name
FROM dba_tables
WHERE owner = 'HR' AND table_name = 'EMPLOYEES';

Use Case 2: Add Partitioning to an Existing Table

Converting a non-partitioned table to a partitioned table is one of the most common uses of DBMS_REDEFINITION.

-- Original non-partitioned table
-- CREATE TABLE hr.orders (order_id NUMBER, order_date DATE, customer_id NUMBER, ...);

-- Step 1: Create the interim table with the desired partition structure
CREATE TABLE hr.orders_interim (
  order_id    NUMBER,
  order_date  DATE,
  customer_id NUMBER,
  amount      NUMBER(12,2),
  status      VARCHAR2(20)
)
PARTITION BY RANGE (order_date) (
  PARTITION p_2022 VALUES LESS THAN (DATE '2023-01-01')
    TABLESPACE app_data,
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01')
    TABLESPACE app_data,
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01')
    TABLESPACE app_data,
  PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01')
    TABLESPACE app_data,
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
    TABLESPACE app_data
)
AS SELECT * FROM hr.orders WHERE 1=0;

-- Step 2: Validate
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('HR', 'ORDERS',
    DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- Step 3: Start redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'HR',
    orig_table   => 'ORDERS',
    int_table    => 'ORDERS_INTERIM',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

-- During a long copy, run SYNC periodically to reduce the final finish time
-- This can be run multiple times
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'ORDERS', 'ORDERS_INTERIM');
  DBMS_OUTPUT.PUT_LINE('Sync completed at: ' || TO_CHAR(SYSDATE));
END;
/

-- Step 4: Copy dependents
DECLARE
  l_errs NUMBER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'HR',
    orig_table       => 'ORDERS',
    int_table        => 'ORDERS_INTERIM',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => l_errs
  );
  IF l_errs > 0 THEN
    -- Review DBA_REDEFINITION_ERRORS
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || l_errs || ' errors. Review DBA_REDEFINITION_ERRORS.');
  END IF;
END;
/

-- Review any errors before finishing
SELECT object_name, base_table_name, ddl_txt, error_num, error_msg
FROM dba_redefinition_errors
WHERE base_table_name = 'ORDERS';

-- Step 5: Final sync and swap
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'ORDERS', 'ORDERS_INTERIM');
END;
/

-- Step 6: Cleanup
DROP TABLE hr.orders_interim;

-- Verify partitions
SELECT table_name, partition_name, num_rows
FROM dba_tab_partitions
WHERE table_owner = 'HR' AND table_name = 'ORDERS'
ORDER BY partition_position;

Use Case 3: Change Compression

-- Create interim table with new compression settings
CREATE TABLE hr.big_table_interim
  COMPRESS FOR OLTP           -- or: ROW STORE COMPRESS ADVANCED
  TABLESPACE app_data
AS SELECT * FROM hr.big_table WHERE 1=0;

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('HR', 'BIG_TABLE',
    DBMS_REDEFINITION.CONS_USE_PK);
END;
/

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'HR',
    orig_table   => 'BIG_TABLE',
    int_table    => 'BIG_TABLE_INTERIM',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

-- Monitor progress
SELECT start_time, end_time, num_rows_completed, num_rows_total
FROM v$online_redef
WHERE table_name = 'BIG_TABLE';

DECLARE l_errs NUMBER; BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    'HR', 'BIG_TABLE', 'BIG_TABLE_INTERIM',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, l_errs);
END;
/

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'BIG_TABLE', 'BIG_TABLE_INTERIM');
END;
/

DROP TABLE hr.big_table_interim;

Use Case 4: Column Reordering and Adding Derived Columns

Use col_mapping to control which columns map to which, add new columns with expressions, or reorder columns in the final table.

-- Add a STATUS_CATEGORY column derived from STATUS during the copy
CREATE TABLE hr.orders_interim (
  order_id         NUMBER,
  order_date       DATE,
  customer_id      NUMBER,
  amount           NUMBER(12,2),
  status           VARCHAR2(20),
  status_category  VARCHAR2(10)   -- NEW column
)
TABLESPACE app_data
AS SELECT * FROM hr.orders WHERE 1=0;

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname      => 'HR',
    orig_table => 'ORDERS',
    int_table  => 'ORDERS_INTERIM',
    -- col_mapping: list of "source_expr target_col" pairs
    col_mapping =>
      'ORDER_ID      ORDER_ID,
       ORDER_DATE    ORDER_DATE,
       CUSTOMER_ID   CUSTOMER_ID,
       AMOUNT        AMOUNT,
       STATUS        STATUS,
       CASE WHEN STATUS IN (''COMPLETE'',''SHIPPED'')  THEN ''CLOSED''
            WHEN STATUS IN (''PENDING'',''PROCESSING'') THEN ''OPEN''
            ELSE ''OTHER''
       END           STATUS_CATEGORY',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

ABORT_REDEF_TABLE

Cancels an in-progress redefinition and cleans up internal structures. The original table is unaffected.

-- Cancel a redefinition in progress
BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE(
    uname      => 'HR',
    orig_table => 'EMPLOYEES',
    int_table  => 'EMPLOYEES_INTERIM'
  );
  DBMS_OUTPUT.PUT_LINE('Redefinition aborted. Original table unchanged.');
END;
/

-- Always drop the interim table after aborting
DROP TABLE hr.employees_interim;

Monitoring Progress

-- Monitor active redefinition jobs
SELECT
  table_owner,
  table_name,
  state,
  start_time,
  ROUND((SYSDATE - start_time) * 24 * 60, 1) AS elapsed_minutes,
  num_rows_completed,
  num_rows_total,
  ROUND(num_rows_completed / NULLIF(num_rows_total, 0) * 100, 1) AS pct_complete
FROM v$online_redef
ORDER BY start_time;

-- Check for errors logged during COPY_TABLE_DEPENDENTS
SELECT
  object_name,
  object_type,
  base_table_name,
  ddl_txt,
  error_num,
  error_msg
FROM dba_redefinition_errors
WHERE base_table_name IN ('EMPLOYEES', 'ORDERS')
ORDER BY object_type, object_name;

-- After FINISH: verify index count matches original
SELECT i.index_name, i.uniqueness, i.status, i.tablespace_name
FROM dba_indexes i
WHERE i.table_owner = 'HR'
  AND i.table_name = 'EMPLOYEES'
ORDER BY i.index_name;

VPD Policy Handling

Tables protected by Virtual Private Database (VPD) policies require special handling because policies are security-sensitive and not automatically cloned.

-- Check existing VPD policies on a table
SELECT policy_name, function, sel, ins, upd, del, enable
FROM dba_policies
WHERE object_owner = 'HR' AND object_name = 'EMPLOYEES';

-- Re-apply the policy to the interim table before FINISH_REDEF_TABLE
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema  => 'HR',
    object_name    => 'EMPLOYEES_INTERIM',
    policy_name    => 'EMP_SEC_POLICY',
    function_schema => 'HR',
    policy_function => 'EMPLOYEE_SECURITY_FN',
    statement_types => 'SELECT, INSERT, UPDATE, DELETE'
  );
END;
/

-- After the swap, remove the original policy from the old table (now EMPLOYEES_INTERIM)
-- The policy on the newly renamed EMPLOYEES table is now active

Best Practices

Run SYNC_INTERIM_TABLE multiple times during long copies (millions of rows). Each sync brings the interim table closer to current, reducing the final finish time.
Test on a non-production copy first. Confirm the interim table definition and column mapping are correct before touching production.
Set ignore_errors => TRUE in COPY_TABLE_DEPENDENTS when dealing with complex schemas, then review DBA_REDEFINITION_ERRORS and manually fix any failed dependents.
Do not run COPY_TABLE_DEPENDENTS more than once. Calling it twice clones dependents twice, resulting in duplicate indexes and constraints.
Use CONS_USE_ROWID only as a last resort. ROWID-based redefinition is slower and disallows certain transformations.
Drop the interim table after ABORT or FINISH. Oracle does not drop it automatically.
Coordinate with application teams. Even though the lock window is brief, the swap changes object IDs — cached statements may need to be re-parsed.

Required Privileges

Action	Privilege Required
Execute DBMS_REDEFINITION	`EXECUTE ON DBMS_REDEFINITION`
Redefine own tables	`CREATE TABLE`
Redefine tables in other schemas	`CREATE ANY TABLE`, `ALTER ANY TABLE`, `DROP ANY TABLE`, `LOCK ANY TABLE`, `SELECT ANY TABLE`
Copy dependents	`CREATE ANY INDEX`, `CREATE ANY TRIGGER` (if applicable)

DBMS_METADATA - Extract DDL of the original table to design the interim table structure
Oracle Data Pump - Alternative for full schema reorganization during maintenance windows
RMAN - Always take a backup before major structural changes

DBMS_REDEFINITION - Online Table Reorganization in Oracle

DBMS_REDEFINITION - Online Table Reorganization in Oracle

Overview

When to Use DBMS_REDEFINITION

How It Works

Prerequisites Check

CAN_REDEF_TABLE

START_REDEF_TABLE

Use Case 1: Move Table to a Different Tablespace

Use Case 2: Add Partitioning to an Existing Table

Use Case 3: Change Compression

Use Case 4: Column Reordering and Adding Derived Columns

ABORT_REDEF_TABLE

Monitoring Progress

VPD Policy Handling

Best Practices

Required Privileges

Related Topics