The Oracle online table reorganization
package, (dbms_redefinition) is used to reorganize
tables while they are accepting updates.
See here for details on using the
dbms_redefinition package for reorganizing Oracle
tables online. The online reorganization
packages does this by creating a snapshot on the
target table and applying all table changes after
the table has been reorganized with the "Create
table as select" command:
-- Check table can be
redefined
EXEC
Dbms_Redefinition.Can_Redef_Table('SCOTT',
'EMPLOYEES');
-- Create new table with CTAS
CREATE TABLE scott.employees2
TABLESPACE tools AS
SELECT empno, first_name,
salary as sal
FROM employees WHERE 1=2;
-- Start Redefinition
EXEC
Dbms_Redefinition.Start_Redef_Table(
-
'SCOTT', -
'EMPLOYEES', -
'EMPLOYEES2', -
'EMPNO EMPNO, FIRST_NAME
FIRST_NAME, SALARY*1.10 SAL);
-- Optionally synchronize new
table with interim data
EXEC
dbms_redefinition.sync_interim_table(
-
'SCOTT', 'EMPLOYEES',
'EMPLOYEES2');
-- Add new keys, FKs and
triggers
ALTER TABLE employees2 ADD
(CONSTRAINT emp_pk2 PRIMARY
KEY (empno)
USING INDEX
TABLESPACE indx);
-- Complete redefinition
EXEC
Dbms_Redefinition.Finish_Redef_Table(
-
'SCOTT', 'EMPLOYEES',
'EMPLOYEES2');
-- Remove original table
which now has the name of the new table
DROP TABLE employees2;
