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

wishing....oh 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 constraints....and DBAs can revoke/grant other schemas objects....
>
> -----Original Message-----
> *From:* Toepke, Kevin M [mailto:ktoepke_at_trilegiant.com]
> *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:JTESTA_at_longaberger.com]
> *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 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 <mailto: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_dmc-it.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Grabowy, Chris
  INET: cgrabowy_at_fcg.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).