Oracle-L: {9i New Features: Online Reorg or DBMS

From: JOE TESTA <JTESTA_at_longaberger.com>
Date: Tue, 14 May 2002 10:58:29 -0800
Message-ID: <F001.00460515.20020514105829@fatcity.com>

Welcome to the next installment of 9i New Features, today's topic is Online changes of objects, specifically we'll cover the new package called DBMS_REDEFINITION.

The spec for this package is located where all of the other package sources are:

ORACLE_HOME/rdbms/admin. The file is dbmshord.sql

So what does this package give us? Well it gives the capability to do online reorganization of a table. Ok so now if you're not confused, you should be :)

In easy to understand terms, in the past when you wanted to move a table to a new tablespace, drop a column, add a column, change a column datatype, it require a exclusive lock on the table during the operation(which if it was a large table could lock it up for a long time). Well that is no longer the case, those kinds of changes can be done while DML is still being applied to the object.

Let's take for an example something that all of us have been asking for YEARS, the rename of a column.

Look at this code, I've included comments within it so its pretty much self-explanatory and you can run it against your 9i database to see what if it really works.

BEGINNING OF SCRIPT -----------------------------------

set serveroutput on size 100000;

let's drop a couple of tables so if we re-run we won't get errors drop table sales; drop table sales_temp;
create a new table, handful of columns with the last one named incorrectly.

create table sales
(sales_id number not null,
sales_amount number(10,2) not null,
salesman_id number(5) not null,
tax_amount number(5,2) not null,
bad_column_name varchar2(20) not null);

add a PK since for online reorg it's required

alter table sales add primary key(sales_id)¯

insert some data insert into sales values(1,20,4,5.70,'bogus'); insert into sales values(2,30,6,6.70,'no way'); insert into sales values(3,40,7,7.70,'XX way'); insert into sales values(4,50,8,8.70,'YY way'); insert into sales values(5,60,9,9.70,'ZZ way'); insert into sales values(6,70,1,0.70,'AA way'); insert into sales values(7,80,2,1.70,'BB way'); insert into sales values(8,90,3,2.70,'CC way'); insert into sales values(9,10,4,3.70,'DD way'); insert into sales values(10,25,5,4.70,'EE way');
commit the data

commit;

run the proc to see if this table can be reorganized online, if we get an error,
then its not possible, otherwise we're good to go.

execute dbms_redefinition.can_redef_table(USER,'SALES');

we must create the temp table for this reorg to happen manually, either with a
create table statement or via a create table as select(no rows please to be copied)
this exercise is going to be to do a rename on the column, so we need to create
the table making sure we have the new column name

create table sales_temp
(sales_id number not null,
sales_amount number(10,2) not null,
salesman_id number(5) not null,
tax_amount number(5,2) not null,
good_column_name varchar2(20) not null);

have to have a PK on the temp table also.

alter table sales_temp add primary key(sales_id);

lets desc the original to see what it looks like before

prompt this is the sales desc before the change desc sales;

ok lets fire up the redefinition, the parms are(SCHEMA, OLD_TBL, TEMP_TBL,
then column mapping, notice we're mapping bad_column_name to good_column_name).

execute dbms_redefinition.start_redef_table(USER,'SALES','SALES_TEMP', - 'sales_id sales_id, sales_amount sales_amount, salesman_id salesman_id, - tax_amount tax_amount, bad_column_name good_column_name');

at this point its YOUR responsiblity to put the triggers, other FK constraints(disabled)
and indexes on the "temp" table before calling the next part to do the "switch".
DO THE OTHER INDEXES, ETC HERE
ok time to finish up

execute dbms_redefinition.finish_redef_table(USER,'SALES','SALES_TEMP');

prompt this is the definition of sales AFTER the change desc sales;
select * from sales;

END OF SCRIPT -----------------------------------

So what you think, pretty kewl(geek kids spelling)?????

Check out the docs on dbms_redefinition for limitations, etc, don't want to give you all the answers.

Until next time all hate mail to /dev/null , all good stuff to 9i_at_oracle-dba.com

Joe

PS: I did receive a request for PGA* init.ora parm, its on the list to do.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: JOE TESTA
INET: JTESTA_at_longaberger.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue May 14 2002 - 13:58:29 CDT

{9i New Features: Online Reorg or DBMS_REDEFINITION Package}