LATEST TOPICS

Restructure an Oracle table online using DBMS_REDEFINITION

Introduction

As a DBA you may have faced situations, where you need to change the column ordering of a particular table. We have different options to accomplish the task. However, most of the methods requires an outage.

Here, I am demonstrating; the method that can be followed to restructure the column order of a particular table without causing a application down time using DBMS_REDEFINITION package

 

Demonstration

Schema Name: test_sch
Table Name: test_tab_red
 

Existing Column Structure

SQL> desc test_tab_red
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(10)
NAME                                               VARCHAR2(25)
ROLE                                               VARCHAR2(15)
EMAIL                                              VARCHAR2(20)
ADDRESS                                            VARCHAR2(100)
JOIN_DATE                                          DATE
SQL> select * from test_tab_red;

ID         NAME                      ROLE            EMAIL                               ADDRESS         JOIN_DATE
---------- ------------------------- --------------- ----------------------------------- --------------- ---------
1          rahul                     DBA             xyz@yahoo.in                        bangalore       07-AUG-14
2          rohit                     Developer       abc@yahoo.in                        bangalore       07-AUG-14

 

Expected Column Structure after re-structuring:

Name                    Null?    Type
----------------------- -------- ----------------
ID                               NUMBER(10)
NAME                             VARCHAR2(25)
JOIN_DATE                        DATE
ROLE                             VARCHAR2(15)
EMAIL                            VARCHAR2(35)
ADDRESS                          VARCHAR2(100) 

 

Note: Here we are changing the order for all columns excpet ID and NAME

Step 1: Check if the table can be redefined/re-structured

SQL> BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST_SCH','TEST_TAB_RED',DBMS_REDEFINITION.CONS_USE_ROWID);
3 END;
4 /

PL/SQL procedure successfully completed.

 

Step 2: Create an interim table with the expected column order for re-structuring

 

2.1: Get the DDL of existing Table
 

SQL> select dbms_metadata.get_ddl('TABLE','TEST_TAB_RED','TEST_SCH') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TAB_RED','TEST_SCH')
--------------------------------------------------------------------------------

CREATE TABLE "TEST_SCH"."TEST_TAB_RED"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(25),
"ROLE" VARCHAR2(15),
"EMAIL" VARCHAR2(35),
"ADDRESS" VARCHAR2(100),
"JOIN_DATE" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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 "USERS"

 

2.2: Create the interim table using the extracted DDL by substituting the column order
 

SQL> CREATE TABLE "TEST_SCH"."TEST_TAB_RED_INTERIM"
( "ID" NUMBER(10,0),
"NAME" VARCHAR2(25),
"JOIN_DATE" DATE,
"ROLE" VARCHAR2(15),
"EMAIL" VARCHAR2(35),
"ADDRESS" VARCHAR2(100)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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 "USERS"
/

Table created.

 

Step 3: Start the redefinition process

SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
('TEST_SCH', 'TEST_TAB_RED','TEST_TAB_RED_INTERIM',
'ID ID,
NAME NAME,
JOIN_DATE JOIN_DATE,
ROLE ROLE,
EMAIL EMAIL,
ADDRESS ADDRESS',
dbms_redefinition.CONS_USE_ROWID
);
END;
/

PL/SQL procedure successfully completed.

 

Step 4: Copy Dependent Objects

SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
(
'TEST_SCH', 'TEST_TAB_RED','TEST_TAB_RED_INTERIM',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors
);
END;
/

PL/SQL procedure successfully completed.

 

Step 5: Query the DBA_REDEFINITION_ERRORS view to check for errors

SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

no rows selected

 

Step 6: Sync the Interim Table

SQL> BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST_SCH', 'TEST_TAB_RED', 'TEST_TAB_RED_INTERIM');
END;
/

PL/SQL procedure successfully completed.

 

Step 7: Complete the Redefinition

SQL> BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST_SCH', 'TEST_TAB_RED', 'TEST_TAB_RED_INTERIM');
END;
/

PL/SQL procedure successfully completed.

 

Step 8: Check the column order of the original (restructured) table

SQL> desc TEST_TAB_RED
Name                    Null?    Type
----------------------- -------- ----------------
ID                               NUMBER(10)
NAME                             VARCHAR2(25)
JOIN_DATE                        DATE
ROLE                             VARCHAR2(15)
EMAIL                            VARCHAR2(35)
ADDRESS                          VARCHAR2(100)
SQL> select * from test_tab_red;

ID         NAME                      ROLE            EMAIL                               ADDRESS         JOIN_DATE
---------- ------------------------- --------------- ----------------------------------- --------------- ---------
1          rahul                     DBA             xyz@yahoo.in                        bangalore       07-AUG-14
2          rohit                     Developer       abc@yahoo.in                        bangalore       07-AUG-14

 

Step 9: Drop the interim table

SQL> drop table TEST_SCH.TEST_TAB_RED_INTERIM purge;

Table dropped.

 

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed