Some table changes, such as partitioning a table or changing column types, normally need a rebuild that blocks users. DBMS_REDEFINITION changes a table's structure online: it copies the data into an interim table with the new design while the original stays in use, keeps them in sync, copies indexes and constraints, and swaps the two in a brief final step.
Code for This Guide
The main examples are in the examples/pkg-data-ops folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_redefinition.can_redef_table(schema, table, dbms_redefinition.cons_use_pk);
dbms_redefinition.start_redef_table(schema, table, interim_table,
col_mapping => 'old_col new_col, ...');
dbms_redefinition.sync_interim_table(schema, table, interim_table); -- optional
dbms_redefinition.copy_table_dependents(schema, table, interim_table,
num_errors => errors_out);
dbms_redefinition.finish_redef_table(schema, table, interim_table);
dbms_redefinition.abort_redef_table(schema, table, interim_table); -- to cancelWiden a Column and Partition the Table
CREW_NOTES gets a longer NOTE column, a new CREATED_ON column with a default, and monthly interval partitioning, all while it could stay in use.
Example:
-- the table to change, while it stays in use
create table crew_notes (
note_id number primary key, employee_id number, note varchar2(200));
insert into crew_notes
select rownum, employee_id, 'Base ' || base_airport from employees;
commit;
-- the new structure: a wider note and a creation date, partitioned by date
create table crew_notes_int (
note_id number,
employee_id number,
note varchar2(1000),
created_on date default date '2026-03-15'
) partition by range (created_on) interval (numtoyminterval(1, 'MONTH'))
(partition p0 values less than (date '2026-01-01'));
declare
v_errors pls_integer;
begin
dbms_redefinition.can_redef_table(user, 'CREW_NOTES', dbms_redefinition.cons_use_pk);
dbms_redefinition.start_redef_table(user, 'CREW_NOTES', 'CREW_NOTES_INT',
col_mapping => 'note_id note_id, employee_id employee_id, note note');
dbms_redefinition.copy_table_dependents(user, 'CREW_NOTES', 'CREW_NOTES_INT',
num_errors => v_errors); -- indexes, constraints, triggers, grants
dbms_redefinition.finish_redef_table(user, 'CREW_NOTES', 'CREW_NOTES_INT');
dbms_output.put_line('dependents copied with ' || v_errors || ' errors');
end;
/
select column_name, data_type, data_length from user_tab_columns
where table_name = 'CREW_NOTES' order by column_id;
select partitioned, (select count(*) from crew_notes) as notes
from user_tables where table_name = 'CREW_NOTES';Output:
Table CREW_NOTES created. 55 rows inserted. Commit complete. Table CREW_NOTES_INT created. dependents copied with 0 errors PL/SQL procedure successfully completed. COLUMN_NAME DATA_TYPE DATA_LENGTH ______________ ____________ ______________ NOTE_ID NUMBER 22 EMPLOYEE_ID NUMBER 22 NOTE VARCHAR2 1000 CREATED_ON DATE 7 PARTITIONED NOTES ______________ ________ YES 55
- CAN_REDEF_TABLE checks that the table can be redefined using its primary key.
- START_REDEF_TABLE copies the rows, with COL_MAPPING listing the columns that carry over.
- COPY_TABLE_DEPENDENTS copies indexes, constraints, triggers, and grants, with 0 errors.
- FINISH_REDEF_TABLE swaps the tables: CREW_NOTES now has the new columns and is partitioned, with all 55 notes.
The example drops both tables afterward.
Things to Know
- Call SYNC_INTERIM_TABLE before FINISH on busy tables, so the final step has little left to copy.
- After FINISH, the interim table holds the old structure and can be dropped.
- Redefinition needs space for a second copy of the table while it runs.
Related Guides
Conclusion
DBMS_REDEFINITION changes a table's structure online through an interim table: start, optionally sync, copy dependents, and finish. Use it for partitioning, column changes, and reorganizations that must not stop the application.
