Re: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
Got Oracle Update from Xephon today (May
2002).
The lead article is about online redefinition by Sameer
Wadhwa
from NuGenesis Technologies.
His article agrees with you.
Yechiel AdarMehish
- Original Message -
From:
JOE
TESTA
To: Multiple recipients of list ORACLE-L
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 10;
-- let's drop a couple of tables so if we re-run we won't get
errorsdrop 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 changedesc 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 changedesc
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 allthe answers.
Until next time all hate mail to /dev/null , all good stuff
to [EMAIL PROTECTED]
Joe
PS: I did receive a request for PGA* init.ora parm, its on the
RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
Joe, I'm of the school of wishing...oh please..oh please...let us be able to
easily rename columns and constraints in the next version
That is until I am disappointed, then I'm of the school of whining and
complaining...@#(*) Oracle sucks...I hate renaming columns...I hate
constraints...I hate 9i.
And then Oracle announces a new version and I'm back in the school of
wishingoh please
-Original Message-
Sent: Tuesday, May 14, 2002 11:03 PM
To: Multiple recipients of list ORACLE-L
Chris, i'm of the school of thought, UNTIL I use it and see it work, its
vaporware.
joe
Grabowy, Chris wrote:
And constraintsand DBAs can revoke/grant other schemas objects
-Original Message-
*From:* Toepke, Kevin M [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 3:48 PM
*To:* Multiple recipients of list ORACLE-L
*Subject:* RE: {9i New Features: Online Reorg or DBMS_REDEFINITION
Package}
A quick followup to this...
I've done some testing of this package and concur with Joe. Its
kewl. Outside of renaming a column, it can be used to quickly
partition a non-partitioned table. Its much faster and easier than
using exchange partition.
The 9iR2 new features whitepaper hints at a native rename column
command.
Caver
-Original Message-
*From:* JOE TESTA [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 2:58 PM
*To:* Multiple recipients of list ORACLE-L
*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 10;
-- 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
RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
Renaming columns? Hmmm.
Sometimes I expect our developer doing unexpected moves. That's why I put
views on top of the tables in such cases. Easier to rename columns :)
Have a good day, guys,
Vadim
-Original Message-
Sent: Wednesday, May 15, 2002 11:19 AM
To: Multiple recipients of list ORACLE-L
Joe, I'm of the school of wishing...oh please..oh please...let us be able to
easily rename columns and constraints in the next version
That is until I am disappointed, then I'm of the school of whining and
complaining...@#(*) Oracle sucks...I hate renaming columns...I hate
constraints...I hate 9i.
And then Oracle announces a new version and I'm back in the school of
wishingoh please
-Original Message-
Sent: Tuesday, May 14, 2002 11:03 PM
To: Multiple recipients of list ORACLE-L
Chris, i'm of the school of thought, UNTIL I use it and see it work, its
vaporware.
joe
Grabowy, Chris wrote:
And constraintsand DBAs can revoke/grant other schemas objects
-Original Message-
*From:* Toepke, Kevin M [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 3:48 PM
*To:* Multiple recipients of list ORACLE-L
*Subject:* RE: {9i New Features: Online Reorg or DBMS_REDEFINITION
Package}
A quick followup to this...
I've done some testing of this package and concur with Joe. Its
kewl. Outside of renaming a column, it can be used to quickly
partition a non-partitioned table. Its much faster and easier than
using exchange partition.
The 9iR2 new features whitepaper hints at a native rename column
command.
Caver
-Original Message-
*From:* JOE TESTA [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 2:58 PM
*To:* Multiple recipients of list ORACLE-L
*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 10;
-- 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
RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
A quick followup to this...
I've done some testing of this package and
concur with Joe. Its kewl. Outside of renaming a column, it can be used to
quickly partition a non-partitioned table. Its much faster and easier than using
exchange partition.
The 9iR2 new features whitepaper hints at a
native rename column command.
Caver
-Original Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 2:58
PMTo: Multiple recipients of list ORACLE-LSubject: {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 10;
-- let's drop a couple of tables so if we re-run we won't get
errorsdrop 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 changedesc 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 changedesc
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 allthe answers.
Until
RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
And constraintsand DBAs can revoke/grant
other schemas objects
-Original Message-From: Toepke, Kevin M
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 3:48
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
{9i New Features: Online Reorg or DBMS_REDEFINITION
Package}
A quick followup to this...
I've done some testing of this package and
concur with Joe. Its kewl. Outside of renaming a column, it can be used to
quickly partition a non-partitioned table. Its much faster and easier than
using exchange partition.
The 9iR2 new features whitepaper hints at
a native rename column command.
Caver
-Original Message-From: JOE TESTA
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, May 14, 2002 2:58
PMTo: Multiple recipients of list ORACLE-LSubject: {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 10;
-- let's drop a couple of tables so if we re-run we won't get
errorsdrop 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 changedesc 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
callin
Re: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}
Chris, i'm of the school of thought, UNTIL I use it and see it work, its
vaporware.
joe
Grabowy, Chris wrote:
And constraintsand DBAs can revoke/grant other schemas objects
-Original Message-
*From:* Toepke, Kevin M [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 3:48 PM
*To:* Multiple recipients of list ORACLE-L
*Subject:* RE: {9i New Features: Online Reorg or DBMS_REDEFINITION
Package}
A quick followup to this...
I've done some testing of this package and concur with Joe. Its
kewl. Outside of renaming a column, it can be used to quickly
partition a non-partitioned table. Its much faster and easier than
using exchange partition.
The 9iR2 new features whitepaper hints at a native rename column
command.
Caver
-Original Message-
*From:* JOE TESTA [mailto:[EMAIL PROTECTED]]
*Sent:* Tuesday, May 14, 2002 2:58 PM
*To:* Multiple recipients of list ORACLE-L
*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 10;
-- 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
