Zero Downtime technique to rebuild Oracle tables

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like:

* Dropping columns
* Shrinking allocated space
* Removing chained blocks and restoring the PCTFREE
* Re-ordering the columns in the table
* Add/remove support for parallel queries
* Add/remove partitioning support
* Swap to/from an Index Organized Table (IOT)

Basically any attribute of a table that you can only change at creation time and would normally require the use of exp/imp, or activities that would take an exclusive lock on the table for the duration of the change.

There are various tools within Oracle that will help with different problems, but the catch-all system is to use DBMS_REDEFINITION.

This tool allows you to:

1) Create a new table, with the attributes you want (New tablespace, Different columns, etc.)
2) Duplicate the data, constraints, and indexes from your current table into the new table... and keep it up-to-date
3) Quickly swap one for the other, with a very short lock, and without any data loss

The magic under the covers is all done with Materialized Views, which allows Oracle to keep the new table in-sync and not lose any data when you swap them.

The basic procedure to remove a column from the SCOTT.REDEF_TEST table:

0) Setup some demo tables to test with... your table will no doubt already exist, so this is just for demo purposes:

--
                      -- Create a dummy table to test with
                      --
                      connect SCOTT
                      create table REDEF_TEST
                             (COL_1 NUMBER(4)     CONSTRAINT REDEF_TEST_PK  PRIMARY KEY,
                              COL_2 VARCHAR2(11),
                              COL_3 DATE          CONSTRAINT REDEF_TEST_NN3 NOT NULL,
                              COL_4 VARCHAR2(11));
                      insert into REDEF_TEST values (1, 'COL_2 ROW_1', TO_DATE('03/01/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (2, 'COL_2 ROW_2', TO_DATE('03/02/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (3, 'COL_2 ROW_3', TO_DATE('03/03/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (4, 'COL_2 ROW_4', TO_DATE('03/04/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (5, 'COL_2 ROW_5', TO_DATE('03/05/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (6, 'COL_2 ROW_6', TO_DATE('03/06/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (7, 'COL_2 ROW_7', TO_DATE('03/07/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (8, 'COL_2 ROW_8', TO_DATE('03/08/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      insert into REDEF_TEST values (9, 'COL_2 ROW_9', TO_DATE('03/09/2012','DD/MM/YYYY'), 'COL4_ROW_1');
                      commit;
                      --
                      create index REDEF_TEST_IX1 on REDEF_TEST(COL_3);

Select all Open in new window

1) Create a new table, with the attributes you want (New tablespace, Different columns, etc.):

--
                      -- Create an interim table (Make any changes needed here, tablespaces, structure, etc)
                      -- Our example table is missing one column "COL_2"
                      --
                      create table REDEF_TEST_INT
                        as select COL_1, COL_3, COL_4 from REDEF_TEST where 1=0;
                      
                      --
                      -- Remove the NOT NULL constraints on the interim table (This will ORA-01451 for PK's that are
                      -- missing when the "INT" table is created, but is needed for all regular NOT NULL columns)
                      --
                      -- select 'alter table REDEF_TEST_INT modify ' || COLUMN_NAME || ' NULL;' from USER_TAB_COLUMNS where TABLE_NAME = 'REDEF_TEST' and NULLABLE='N';
                      --
                      alter table REDEF_TEST_INT modify COL_1 NULL;
                      alter table REDEF_TEST_INT modify COL_3 NULL;
                      --
                      desc REDEF_TEST;
                      desc REDEF_TEST_INT;
                      
                      --
                      -- Determine if the Original table can be Redefined online (No output is good news…)
                      --
                      connect / as sysdba
                      set serveroutput on
                      exec DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','REDEF_TEST');

Select all Open in new window

2) Duplicate the data, constraints, and indexes from your current table into the new table... and keep it up-to-date:

--
                      -- Begin the redefinition (Copy the table)
                      --
                      exec DBMS_REDEFINITION.START_REDEF_TABLE( -
                        uname       => 'SCOTT', -
                        orig_table  => 'REDEF_TEST', -
                        int_table   => 'REDEF_TEST_INT', -
                        col_mapping => 'COL_1 COL_1, COL_3 COL_3, COL_4 COL_4');
                      
                      --
                      -- Examine the results 
                      --
                      select 'REDEF_TEST' TABLE_NAME, count(*) COUNT, max(COL_1) MAX_COL_1 from SCOTT.REDEF_TEST
                      union all
                      select 'REDEF_TEST_INT' TABLE_NAME, count(*) COUNT, max(COL_1) MAX_COL_1 from SCOTT.REDEF_TEST_INT;
                      --
                      desc SCOTT.REDEF_TEST;
                      desc SCOTT.REDEF_TEST_INT;
                      
                      --
                      -- If the table had dependencies (Indexes, Constraints, Triggers)
                      -- this would be the point at which they would have been copied
                      --
                      DECLARE
                        error_count pls_integer:=0;
                      BEGIN
                        DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT',
                                                                'REDEF_TEST',
                                                                'REDEF_TEST_INT',
                                                                DBMS_REDEFINITION.CONS_ORIG_PARAMS,
                                                                TRUE,
                                                                TRUE,
                                                                TRUE,
                                                                FALSE,
                                                                error_count);
                                                                DBMS_OUTPUT.PUT_LINE('errors:='||TO_CHAR(error_count));
                      END;
                      /
                      
                      --
                      -- Fix the NOT NULL constraints on the interim table (because DBMS_REDEFINITON doesn't validate them Note: 1089860.1)
                      -- (Bug number 13655454 has been filed... please call Oracle and request it get fixed!) 
                      --
                      -- select 'alter table SCOTT.REDEF_TEST_INT' SQL_TEXT from DUAL
                      -- union
                      -- select * from (select '  enable VALIDATE constraint ' || CONSTRAINT_NAME SQL_TEXT from DBA_CONSTRAINTS where OWNER = 'SCOTT' and TABLE_NAME = 'REDEF_TEST_INT' order by CONSTRAINT_NAME);
                      --
                      alter table SCOTT.REDEF_TEST_INT                               
                        enable VALIDATE constraint TMP$$_REDEF_TEST_PK0,
                        enable VALIDATE constraint TMP$$_REDEF_TEST_NN30;
                      --
                      desc SCOTT.REDEF_TEST;
                      desc SCOTT.REDEF_TEST_INT;
                      
                      --
                      -- Build statistics on the interim table (8 parallel threads, pick your own number)
                      --
                      exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCOTT', tabname=>'REDEF_TEST_INT', estimate_percent=>100, cascade=>true, degree=>8);

Select all Open in new window

3) Quickly swap one for the other, with a very short lock, and without any data loss:

--
                      -- Sync the interim table (Just to make sure it's all up-to-date, to keep the final step short)
                      --
                      exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'REDEF_TEST', 'REDEF_TEST_INT');
                      
                      --
                      -- Finish the Redefinition (Swap the tables) 
                      --
                      exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','REDEF_TEST','REDEF_TEST_INT'); 
                      
                      --
                      -- Examine the results 
                      --
                      select 'REDEF_TEST' TABLE_NAME, count(*) COUNT, max(COL_1) MAX_COL_1 from SCOTT.REDEF_TEST
                      union all
                      select 'REDEF_TEST_INT' TABLE_NAME, count(*) COUNT, max(COL_1) MAX_COL_1 from SCOTT.REDEF_TEST_INT;
                      --
                      desc SCOTT.REDEF_TEST;
                      desc SCOTT.REDEF_TEST_INT;
                      
                      --
                      -- Drop the Interim table 
                      --
                      -- connect SCOTT
                      -- drop table REDEF_TEST_INT;
                      -- purge recyclebin;

Select all Open in new window

So there you have it, a simple and Oracle Supported mechanism to make any structural change you could ever want, and all it will cost you is a momentary lock... the users will be none the wiser!  This facility has been around since v9.0.1, so that includes 9i, 10g, 11g, which should cover most databases in use today.  It's long history also means that it's a tried and tested, albeit rarely used piece of Oracle.

Alternative mechanisms like "safe and dependable" exp/imp, "create table as select", also carry risks, but they also carry the large penalty of downtime.

As always, test, test, and test again, because performing surgery on your production data whilst it's in use has many pitfalls, but if you're running a 24/7 system then sometimes you don't have any other viable option.