techblog 

Introduction

Oracle realizes that these days the up-time of applications is critical. Therefore they have developed ways to reduce the downtime for applications during application upgrades. At Nominet we have had some software releases that took longer than expected and we have had some table redefinitions during upgrades that run for several hours. That makes it worthwhile to have a look at Oracle’s methods to limit the disruption for the up-time of applications. This article describes a way to redefine Oracle table structures and data while that table remains online and accessible for reads and writes in its original form for Live applications.

What can you do

Oracle has created a PL/SQL package called DBMS_REDEFINITION that allows you to restructure a table definition and transform the data in a table. That means you can:

• Add columns
• Drop columns
• Change the data type or length of columns
• Transform the data inside the table using standard and user-written PL/SQL functions

When is this handy

This capacity is very useful when you want to change a big table that is often used by an application. The changes you want to apply will cause an unacceptable amount of downtime by creating many locks on the table or making it inaccessible for the application otherwise.

How does it work

You create an empty table with a different name than the source table and with the new structure that you want to achieve. This table need not have any of the constraints, grants or indexes that the source table has. You then start the redefinition using the PL/SQL package DBMS_REDEFINITION. This creates a materialized view with a materialized view log in the background. These two structures capture all the changes to the source table while you are transforming the data into the empty table you have created before the redefinition started. Once the transformation is finished you can let Oracle copy all the indexes, constraints and grants to the new table with one procedure call. The last step is the renaming of the new table to the original name. This is the only moment that the table will be locked for a millisecond.

Example

Let’s first create a source table and fill it with some data. We’ll also add an index and grant to prove that these will also be copied to the new transformed table.

create table customer(
 id               number      primary key
,name             varchar2(30)
,address          varchar2(30)
,phone_number     varchar2(10)
,country_prefix   varchar2(5)
,county_id        number
,credit           number
);

create index cust_name_idx on customer(name);grant select on customer to public;

truncate table customer;

begin
   for i in 1..3
   loop
      insert into customer values (i, 'ARJAN', 'OXFORD', rpad(i, 10, i), '0044', i, i*1000);
   end loop;
end;
/

commit;

Let’s look at the data before transformation.

select * from customer;

        ID NAME                           ADDRESS                        PHONE_NUMB COUNT  COUNTY_ID     CREDIT
---------- ------------------------------ ------------------------------ ---------- ----- ---------- ----------
         1 ARJAN                          OXFORD                         1111111111 0044           1       1000
         2 ARJAN                          OXFORD                         2222222222 0044           2       2000
         3 ARJAN                          OXFORD                         3333333333 0044           3       3000

3 rows selected.

Now let’s start the redefinition process. First we’ll create a user-defined PL/SQL function to show that you can make customized transformation to your data.

create or replace function transform_credit(p_credit number)
return number
is

begin
   return ((p_credit/3) + 10);
end;
/

At this point we are ready to go through the redefinition process. This entails 3 calls to procedures within the DBMS_REDEFINITION package. The first call starts the process and defines the column mapping between the source and target table.

create table customer_tmp(
 id               number
,name             varchar2(30)
,address          varchar2(30)
,phone_number     varchar2(15)
,county_id        number
,credit           number
);

You can see that we are doing the following transformations:

• Using 2 Oracle defined functions to change the columns “NAME” and “ADDRESS”
• Concatenating the country code to the phone number and in effect dropping the column country_prefix
• Transforming the value in the column credit with our user-defined function

Suppose that this transformation will take several hours and changes are being made by a Live application to the data in the source table that we don’t want to loose. To prove that these data will be in the transformed table we’ll insert some more records into the source table from a different session.

begin
for i in 4..6
loop
   insert into customer values (i, 'JAN', 'AMSTERDAM', rpad(i, 10, i), '0031', 1, i*1000);
end loop;
end;
/
commit;

Once the transformation is finished we can copy all indexes, constraints and grants to the target table with one simple call.

set serveroutput on

declare
   l_errors number;
begin
  dbms_redefinition.copy_table_dependents
  ( user, 'CUSTOMER', 'CUSTOMER_TMP',
    copy_indexes => dbms_redefinition.cons_orig_params,
    num_errors => l_errors );

  dbms_output.put_line('Errors: '||l_errors);
end;
/

During this transformation the source table has been available for reads and writes for the Live application. We are now ready to rename the table so the application can read and write from the new target table.

begin
    dbms_redefinition.finish_redef_table( user, 'CUSTOMER', 'CUSTOMER_TMP' );
end;
/

Let’s look at the result.

select * from customer;
        ID NAME                           ADDRESS                        PHONE_NUMBER     COUNTY_ID     CREDIT
---------- ------------------------------ ------------------------------ --------------- ---------- ----------
         1 Arjan                          Oxford                         00441111111111           1 343.333333
         2 Arjan                          Oxford                         00442222222222           2 676.666667
         3 Arjan                          Oxford                         00443333333333           3       1010
         4 Jan                            Amsterdam                      00314444444444           1 1343.33333
         5 Jan                            Amsterdam                      00315555555555           1 1676.66667
         6 Jan                            Amsterdam                      00316666666666           1       2010

6 rows selected.

As you can see the data have been transformed and all changes made to the source table during the transformation have also been transformed and migrated to the new target table.

Limitations

It would be great if you could add a column and fill it with values selected from a different table (for example when you want to denormalize a table). This is not possible though mainly due to the limits on materialized views in Oracle.

Conclusion

The use of DBMS_REDEFINITION can decrease the downtime of your applications. Its use is quite simple compared to inventing and coding this functionality manually. It cannot be used in all circumstances, but it is certainly a worthwhile addition to the toolkit of any developer or DBA.

I’ve used a few Continuous Integration (CI) servers over the years. Basically, a CI server will download your projects source, build and run the tests, and then publish the results (and optionally email you). The trigger can be time-based, VCS-trigger-based, or manual. This allows you constantly to monitor the state of your project’s health.

I used CruiseControl for a while - once I got it working, it worked well. It was pretty clunky to set up, though.

I’ve now been using Hudson for a wee while, and love it. It couldn’t be easier to install (simply run a JAR file), and the configuration is done entirely through a web interface that is a pleasure to interact with. So far, I’ve had no problems with it at all.

I’ve heard grumbles from folks using Teamcity in relation to its preference for using Ant as a build script. Indeed, it seems some folk actually write an Ant harness to call their makefile from Teamcity! I’ve been building and testing Java and Ruby directly from Hudson, with no issues.

Given that Hudson is free and open-source, I’d highly recommend it.

I’ve been asked why Dnsruby is hosted on Rubyforge; asked often enough to write a note here!

I could have hosted Dnsruby on Nominet’s servers (perhaps using TRAC and subversion). However, if I were looking for a useful open-source library, I’d prefer to see it hosted on a well-known public repository. That way, I’d know what I could expected from the hosting site (fora, trackers, source repositories, downloads, etc.). I’d also know that the information which had been published to that site already, would stay on that site in the future. I’d have less confidence about a privately hosted project.

These reasons are enough for me to host dnsjnio on sourceforge (indeed, the Apache JAMES SPF folks were unable to use the project until it was moved to sourceforge from Nominet’s site).

However, for Dnsruby, there is an additional advantage to using Rubyforge - simple interaction with gems. I can publish a new release of my project to rubyforge, and within an hour or two, anyone can type “gem install dnsruby” and have the latest version installed to their machine.

The downside of not using Nominet to host these projects is that it’s not always obvious what great open-source projects we produce!