Zero Downtime technique to rebuild Oracle tables

Greg CloughSenior Oracle DBA
Published:
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);

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');

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);

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;

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.
4
10,575 Views
Greg CloughSenior Oracle DBA

Comments (6)

Greg CloughSenior Oracle DBA

Author

Commented:
I suspect they will, but as with all invalid objects the first time they are used they will automatically re-compile.  If you wanted to avoid this small delay the first time something invalid is used, then you could always run utlrp.sql immediately after the FINISH_REDEF_TABLE step.
CERTIFIED EXPERT

Commented:
ok.. thanks greg..

There might be a possibility that tables which we use using this approach to redefine or rebuild may be used by some packages or procedures which are used in code in frequently used screens(by front liners)... i think even if we follow this approach, those screens which are frequently used will have an effect,

yeah we can use utlrp.sql for that(to recompile).. thanks for the article..

Commented:
that's true wasim.
 redef approach makes invalid and causes panic in application functionality. we can always compile the invalid manually or through utlrp.sql , but the trouble is recompile takes long time when done as an online action while users are connected and application is being used. Same compilation goes well quickly when  connection sessions are not in database.
one workaround idea. Use the Redef procedure and immediately restart the database in restricted mode and quickly compile.(this db restart can be a planned shutdown) and bring up normal.
Greg CloughSenior Oracle DBA

Author

Commented:
If you have concurrency issues and can't endure the automatic recompile on first use of any object (package, procedure, etc.) that depends on your table, then yes, you can take a very short downtime during the final DBMS_REDEFINITION.FINISH_REDEF_TABLE step.  Think of it something like:

1.

Spend hours or days building your new table, all the while your database is 100% available

2.

Suspend access for 5 minutes (or less), whilst you run FINISH_REDEF_TABLE and then utlrp.sql for good measure

3.

Release your database for use againInstead of hours or days of downtime, you've got it shrunk to a fixed limit of a few minutes.
Mark GeerlingsDatabase Administrator, retired
CERTIFIED EXPERT

Commented:
I've used this approach a few times, but now I have a case where the table I want to "un-chain" rows in is referenced in materialized views.  Does the DBMS_REDEFINITION approach keep the materialized views in-sync/updated?  The call to DBMS_REDEFINITION.CAN_REDEF_TABLE for this table returns nothing, so that indicates "no problem", correct?  When we tested this in our 11.2 database, it looks like the materialized views end up pointing to the old table, and not the new one.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.