DBMS_REDEFINITION Is Good Option For Zero Downtime Environments

Tried DBMS_REDEFINITION for the table structure change activity. This is almost an no downtime activity and was able to succeed. 
Below are the steps done

Except Step 5, all other step can be performed online.


1) Check table can be redefined

EXEC Dbms_Redefinition.Can_Redef_Table('XX', 'XX_ITEM_COST_INT');

PL/SQL procedure successfully completed.

2) Create intermediate table without any constraints and index (change the INITRANS,PCTFREE value as required )

CREATE TABLE "XX"."XX_ITEM_COST_INT_2"
   (    "ODI_RUN_ID" NUMBER,
        "ORG_TYPE" VARCHAR2(10) NOT NULL ENABLE,
        "ORG" VARCHAR2(40) NOT NULL ENABLE,
.
.
   ) PCTFREE 25 PCTUSED 40 INITRANS 15 MAXTRANS 255
  STORAGE(
.
.
COMPRESS FOR OLTP LOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "XX_TS_TX_DATA" )  
  Table created.

3) start redefine table:

EXEC Dbms_Redefinition.Start_Redef_Table('XX','XX_ITEM_COST_INT','XX_ITEM_COST_INT_2');

PL/SQL procedure successfully completed.

Elapsed: 00:08:30.85

4) Add Constraints and indexes: (change the INITRANS,PCTFREE value as required )

alter table "XX"."XX_ITEM_COST_INT_2" add
(CONSTRAINT "XX_CST_SCDH_ITEM_COST_PK_2" PRIMARY KEY ("ODI_RUN_ID", "ITEM_NUM","COST_TYPE", "ORG")
USING INDEX PCTFREE 25 INITRANS 15 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "XX_TS_TX_DATA");
  
  Table altered.

Elapsed: 00:10:13.45

CREATE INDEX "XX"."XX_CST_SCDH_ITEM_PROCESS_2" ON "XX"."XX_ITEM_COST_INT_2" ("PROCESS_FLAG")
  PCTFREE 25 INITRANS 15 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "XX_TS_TX_IDX";
  
Elapsed: 00:03:02.62

  CREATE INDEX "XX"."XX_CST_SCDH_ITEM_PROCESS_2_2" ON "XX"."XX_ITEM_COST_INT_2" ("ITEM_NUM", "ORG")
  PCTFREE 25 INITRANS 15 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "XX_TS_TX_IDX"

Elapsed: 00:04:09.39
  

5) Finish the redefinition

new   1: select OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024/1024 from dba_segments where SEGMENT_NAME='XX_ITEM_COST_INT_2' group by OWNER,SEGMENT_NAME
XX
XX_ITEM_COST_INT_2
               1.94921875 –(with new storage clause size got increased)\

new   1: select OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024/1024 from dba_segments where SEGMENT_NAME='XX_ITEM_COST_INT' group by OWNER,SEGMENT_NAME
XX
XX_ITEM_COST_INT
               1.79882813

EXEC Dbms_Redefinition.Finish_Redef_Table( 'XX', 'XX_ITEM_COST_INT', 'XX_ITEM_COST_INT_2');

PL/SQL procedure successfully completed.

new   1: select OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024/1024 from dba_segments where SEGMENT_NAME='XX_ITEM_COST_INT_2' group by OWNER,SEGMENT_NAME
XX
XX_ITEM_COST_INT_2
               1.79882813
new   1: select OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024/1024 from dba_segments where SEGMENT_NAME='XX_ITEM_COST_INT' group by OWNER,SEGMENT_NAME
XX
XX_ITEM_COST_INT –(table got redefined)
               1.94921875

6) Tidy up the work

DROP TABLE XX.XX_ITEM_COST_INT_2;
ALTER TABLE XX.XX_ITEM_COST_INT RENAME CONSTRAINT XX_CST_SCDH_ITEM_COST_PK_2 TO XX_CST_SCDH_ITEM_COST_PK;
ALTER INDEX XX.XX_CST_SCDH_ITEM_PROCESS_2 RENAME TO XX_CST_SCDH_ITEM_PROCESS_IDX1;
ALTER INDEX XX.XX_CST_SCDH_ITEM_PROCESS_2_2 RENAME TO XX_CST_SCDH_ITEM_PROCESS_IDX2;

References:- http://www.oracle.com/us/corporate/demantra-improve-performance-1900058.pdf

HAPPY LEARNING!