RE: Renaming columns ...

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Tue, 05 Jun 2001 15:05:16 -0700
Message-ID: <F001.0031D0FF.20010605150605@fatcity.com>

It is
not so easy if there are constraints and indexes on the table - especially foreign key constraints. You will need to create scripts to recreate constraints and indexes and also you may need to drop table_name_2 cascade constraints. Also if you had sinonyms defined on original table - you will need to drop and recreate them. I can also mention snapshots/materialized_views. Also all PLSQL objects depending on this table will be invalidated and need to be recompiled etc.

Alex
Hillman



-----Original Message-----From: lhoska_at_calibresys.com [mailto:lhoska_at_calibresys.com]Sent: Tuesday, June 05, 2001 6:14 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Renaming columns ...

  This
  will work:
  RENAME TABLE_NAME TO table_name_2;

create table TABLE_NAMEas select

from table_name_2; DROP TABLE table_name_2; Note you don't need to issue a 'commit' after after create table or drop table. Also, when you do create table as select from another table there is no need to specify the columns. The new table you created will have the same columns as the tables you're selecting your data from.

-----Original Message-----From: Helen rwulfjeq [mailto:lannyue_at_yahoo.com]Sent: Tuesday, June 05, 2001 3:45 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Renaming columns ...
 will this work?
 RENAME TABLE_NAME TO table_name_2;
 create table TABLE_NAME( column_name_you_want...)as select * from table_name_2;COMMIT;
 DROP TABLE table_name_2;COMMIT;
 HTH
 "Deshpande, Kirti"
 <kirti.deshpande_at_verizon.com> wrote: <BLOCKQUOTE
 style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">Hi

      Raj,1. There is a way to do it. And Mr. Steve A. has some good info 
      about it (&its implications) on his web site. (Sorry I do not have 
      a direct URL link toit). I believe this works for all versions. 2. 
      Yes, it does. I have played with it on my test database.However, I 
      will not do this on a Production database as it involves adirect DML 
      to modify sys.COL$. And Oracle does not recommend doing suchthings to 
      your databases. I am not sure if renaming column is supported in 
      9i. Anyone else know forsure? If you are using 8i, the safe 
      way to address this is to add a new column tothe table, populate it 
      with the contents from the old, and drop the oldcolumn. If the column 
      is not nullable, and if there are plenty of nullablecolumns towards 
      the end of the table, I suggest considering reorganizationof the 
      table. I just did that (! ! reorg) in the last couple of weeks with 
      a60Mil row table to get the new & not nullable columns towards the 
      'head' ofthe table. HTH,Regards,- Kirti 
      Deshpande Verizon Information 
      Serviceshttp://www.superpages.com> -----Original 
      Message-----> From: Jamadagni, Rajendra 
      [SMTP:Rajendra.Jamadagni_at_espn.com]> Sent: Monday, June 04, 2001 
      8:51 AM> To: Multiple recipients of list ORACLE-L> Subject: 
      Renaming columns ...> > Hi all,> > although I 
      am searching information on Metalink and OTN, I just want to> 
      ask> this question to the list ...> > 1. Is it 
      possible to rename a column? (we have lots of columns that need> 
      to> be renamed). Which version supports it?> 2. Does 
      renaming automatically takes care of indexes?> > Coding will 
      be a lot of manual labor, but that's okay we can handle it.> 
      > Thanks in advanc! ! e> Raj> 
      ______________________________________________________> Rajendra 
      Jamadagni MIS, ESPN Inc.> Rajendra dot Jamadagni at ESPN dot 
      com> Any opinion expressed here is personal and doesn't reflect 
      that of ESPN> Inc.> > QOTD: Any clod can have facts, 
      but having an opinion is an art !> -- Please see the 
      official ORACLE-L FAQ: http://www.orafaq.com-- Author: Deshpande, 
      KirtiINET: kirti.deshpande_at_verizon.comFat City Network 
      Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- 
      Public Internet access / Mailing 
      Lists--------------------------------------------------------------------To 
      REMOVE yourself from this mailing list, send an E-Mail messageto: 
      ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe 
      message BODY, include a line containing: UNSUB ORACLE-L(or the name of 
      mailing list you want to be removed from). You mayalso send the! ! 
      HELP command for other information (like subscribing).

Do You Yahoo!?<A
href="http://personal.mail.yahoo.com/?.refer=mailiyfoot">Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail. Received on Tue Jun 05 2001 - 17:05:16 CDT