Convert a Large Oracle Table to a Partitioned Table with DBMS_REDEFINITION

Oracle recommends using partitioned tables for objects larger than 2 GB because they offer better management and performance. Four conversion methods exist, but this article focuses on the online redefinition approach (method D), which is widely used for converting regular tables to partitioned tables without long downtime.

Overview of the Online Redefinition Process

The process creates a new partitioned table, moves data from the original table, transfers dependent objects, and finally renames the tables. The example uses the SCOTT schema's EMP table.

Step‑by‑Step Procedure

Verify that the table can be redefined

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

Create a temporary partitioned table

CREATE TABLE SCOTT.EMP_1 (
  EMPNO   NUMBER(4),
  ENAME   VARCHAR2(10 BYTE),
  JOB     VARCHAR2(9 BYTE),
  MGR     NUMBER(4),
  HIREDATE DATE,
  SAL     NUMBER(7,2),
  COMM    NUMBER(7,2),
  DEPTNO  NUMBER(2)
)
PARTITION BY RANGE (DEPTNO) (
  PARTITION EMP_A1 VALUES LESS THAN (20),
  PARTITION EMP_A2 VALUES LESS THAN (30),
  PARTITION EMP_A3 VALUES LESS THAN (40),
  PARTITION EMP_A4 VALUES LESS THAN (50),
  PARTITION EMP_A5 VALUES LESS THAN (60)
);

Start the data migration

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_1');

Synchronize interim changes (optional for large tables)

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMP', 'EMP_1');
END;
/

Copy dependent objects (indexes, constraints, triggers, etc.)

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    'SCOTT', 'EMP', 'EMP_1',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

Check for errors

SELECT object_name, base_table_name, ddl_txt FROM DBA_REDEFINITION_ERRORS;

Finish the redefinition

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP', 'EMP_1');
END;
/

Conclusion and Tips

A test on a 2.3 GB table with 3.6 million rows completed in about 56 seconds, demonstrating the method’s speed. In production, thorough testing is essential to estimate execution time and verify correctness. If an error occurs, abort the operation with:

BEGIN
  DBMS_REDEFINITION.ABORT_REDEF_TABLE(
    uname => 'SCOTT',
    orig_table => 'EMP',
    int_table => 'EMP_1'
  );
END;

After a successful conversion, the original table is replaced by the new partitioned version, providing improved manageability for large datasets.