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 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:[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 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 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > > > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Vadim Gorbounov INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
