----- Original Message -----
Sent: Tuesday, May 14, 2002 8:58 PM
Subject: {9i New Features: Online Reorg
or DBMS_REDEFINITION Package}
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.
Joe
PS: I did receive a request for PGA* init.ora parm, its on the list to
do.