Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: {9i New Features: Online Reorg or DBMS_REDEFINITION Package}

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

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Tue, 14 May 2002 11:48:22 -0800
Message-ID: <F001.004605D2.20020514114822@fatcity.com>


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-----
Sent: Tuesday, May 14, 2002 2:58 PM
To: Multiple recipients of list ORACLE-L

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.  

set serveroutput on size 100000;  

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);  

alter table sales add primary key(sales_id)-  

commit;  

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

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);  

alter table sales_temp add primary key(sales_id);  

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

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');  

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;  

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: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.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 - 14:48:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US