RE: Renaming columns ...

I have done this on 8.1.6 by :

   Altering the table to add the new column;    Copying the contents of the old column to the new one;    Altering the table to drop the old column...

lhoska_at_calibresys.com_at_fatcity.com on 06/05/2001 06:13:49 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

This will work:

RENAME TABLE_NAME TO table_name_2;

create table TABLE_NAME
as 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 PM
     To: Multiple recipients of list ORACLE-L
     Subject: 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:
      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 to
      it). 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 a
      direct DML to modify sys.COL$. And Oracle does not recommend doing
      such
      things to your databases.

      I am not sure if renaming column is supported in 9i. Anyone else know
      for
      sure?

      If you are using 8i, the safe way to address this is to add a new
      column to
      the table, populate it with the contents from the old, and drop the
      old
      column. If the column is not nullable, and if there are plenty of
      nullable
      columns towards the end of the table, I suggest considering
      reorganization
      of the table. I just did that (! ! reorg) in the last couple of weeks
      with a
      60Mil row table to get the new & not nullable columns towards the
      'head' of
      the table.

      HTH,

      Regards,

      - Kirti Deshpande
      Verizon Information Services
      http://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, Kirti
      INET: kirti.deshpande_at_verizon.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).



     Do You Yahoo!?
     Yahoo! Mail Personal Address - Get email at your own domain with
     Yahoo! Mail.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Ken.Eaton_at_dankasi.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).