<

[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x

Zero Downtime technique to rebuild Oracle tables

Published on
15,062 Points
7,162 Views
4 Endorsements
Last Modified:
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
Comment
Author:Greg Clough
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
wow.. good approach.. have a doubt here..

during this approach.. if there are any stored procs or package body's which are using my table, won't they get invalid??
0
 
LVL 6

Author Comment

by:Greg Clough
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.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
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..
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 5

Expert Comment

by:Anand
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.
0
 
LVL 6

Author Comment

by:Greg Clough
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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
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.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Join & Write a Comment

Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month