Online Table Redefinition – Sample Execution

When we want to get rid of some of the columns of a table but the table is being used heavily, we can resort to DBMS_REDEFINITION

Assume that we want to drop the “ENTER_DATE” from the CUSTOMERS table in an online fashion…

CREATE A TEST TABLE

CREATE TABLE DBA_ILKER.TBL_CUSTOMERS
(
  CID          INTEGER,
  NAME         VARCHAR2(100 BYTE),
  CREATE_DATE  DATE                             DEFAULT sysdate,
  ISSUE_DATE   TIMESTAMP(6) WITH TIME ZONE      DEFAULT systimestamp
);

ALTER TABLE DBA_ILKER.TBL_CUSTOMERS ADD 
CONSTRAINT CID_PK
 PRIMARY KEY (CID)
 ENABLE
 VALIDATE;

ADD SOME DATA

Insert into DBA_ILKER.TBL_CUSTOMERS(CID, NAME) Values(1, 'John Malkovic');
Insert into DBA_ILKER.TBL_CUSTOMERS(CID, NAME) Values(2, 'James Belushi');
Insert into DBA_ILKER.TBL_CUSTOMERS(CID, NAME) Values(3, 'Michael Moore');
Insert into DBA_ILKER.TBL_CUSTOMERS(CID, NAME) Values(4, 'David Hume');
Insert into DBA_ILKER.TBL_CUSTOMERS(CID, NAME) Values(5, 'Richard Feynman');
COMMIT;

– REDEFINITION STEPS –

CHECK THAT THE TABLE CAN BE REDEFINED

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'DBA_ILKER',
    tname        => 'TBL_CUSTOMERS',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/

CREATE THE TABLE IMAGE

CREATE TABLE "DBA_ILKER"."X_TBL_CUSTOMERS" 
AS
SELECT * from "DBA_ILKER"."TBL_CUSTOMERS" 
WHERE 0 = 1;

DROP THE UNDESIRED COLUMNS FROM THE NEW IMAGE TABLE

ALTER TABLE "DBA_ILKER"."X_TBL_CUSTOMERS" DROP (ISSUE_DATE);

START THE REDEFINITION

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'DBA_ILKER', 
    orig_table   => 'TBL_CUSTOMERS',
    int_table    => 'X_TBL_CUSTOMERS',
    col_mapping  => 'CID CID,NAME NAME,CREATE_DATE CREATE_DATE',
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/



DECLARE
  err_count PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'DBA_ILKER', 
    orig_table       => 'TBL_CUSTOMERS',
    int_table        => 'X_TBL_CUSTOMERS',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
    copy_triggers    => TRUE, 
    copy_constraints => TRUE, 
    copy_privileges  => TRUE, 
    ignore_errors    => TRUE, 
    num_errors       => err_count);
    DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(err_count));
END;
/



BEGIN 
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'DBA_ILKER', 
    orig_table => 'TBL_CUSTOMERS', 
    int_table  => 'X_TBL_CUSTOMERS');
END;
/



BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'DBA_ILKER', 
    orig_table => 'TBL_CUSTOMERS', 
    int_table  => 'X_TBL_CUSTOMERS');
END;
/

DROP THE AUXILIARY IMAGE TABLE

drop table DBA_ILKER.X_TBL_CUSTOMERS cascade constraints purge;