[ORACLE 11G] DBMS_REDEFINITION Table Online.

Step 1: Verify that the table is a candidate for online redefinition

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE ('SCHEMA', 'TABLE_ORGIN', DBMS_REDEFINITION.CONS_USE_PK);
END;
/


Step 2: Create an interim table


create table SCHEMA.TABLE_INTERIM
partition by hash(zip) partitions 8
as
select * from movies.customer where 1=0;


---1. Select * from table where 1=0  --false condition return just header of the fields (attribute) but with 0 rows
---2. Select * from table where 1=1 --true condition return whole table entries this is same as select * from table

Step 3: Enable parallel DML operations

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

Step 4: Start the redefinition process

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM');
END;
/

dbms_redefinition.start_redef_table('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM','COLUM1_SRC COLUM1_DEST,COLUM2_SRC COLUM2_DEST');



Step 5: Copy dependent objects

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SCHEMA','TABLE_ORIGIN','TABLE_INTERIM',DBMS_REDEFINITION.CONS_ORIG_PARAMS,TRUE,TRUE,TRUE,TRUE,num_errors);
END;


Step 6: Check for any errors

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;


Step 7: Synchronize the interim table (optional)

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('SCHEMA', 'TABLE_ORIGIN', 'TABLE_INTERIM');
END;
/

Step 8: Complete the redefinition

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SCHEMA', 'TABLE_ORIGIN', 'TABLE_INTERIM');
END;
/

Step 9: Drop the interim table

drop table SCHEMA.TABLE_INTERIM cascade constraints purge;

Step 10: Analyze Table

BEGIN
  SYS.DBMS_STATS.GATHER_TABLE_STATS (
      OwnName        => 'SCHEMA'
     ,TabName        => 'TABLE_ORIGIN'
    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE
    ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'
    ,Degree            => 4
    ,Cascade           => TRUE
    ,No_Invalidate     => FALSE);
END;
/