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.
Loading ...
