Re: Rename a column

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Mon, 20 Apr 1998 16:03:09 GMT
Message-ID: <353b6f49.31945092@www.sigov.si>

On Mon, 20 Apr 1998 13:21:48 GMT, tim.hall_at_spam.begone (Tim Hall) wrote:

>On Mon, 20 Apr 1998 21:00:35 +0800, "Lui Yuan Tze"
><ssplyt_at_pacific.net.sg> wrote:
>
>>Hi,
>>How does one rename a table column ?
>>
>>Cheers
>>
>
>You can't. You have to rename the table, then create a new table with
>your desired new column in it and copy the data across (You can do
>this with create table <TABLE> as select * from <OLD_TABLE>)
>

You can do it, if you are willing to take a risk of messing around with Oracle's fixed tables. If anything goes wrong, I'm afraid youl be on your own, don't rely on Oracle Support. So here it goes:

Assume you want to rename column A FROM TABLE SCOTT.TEST_TABLE to B:

1. take a clean backup of your database;
2. Connect as SYS or internal;
3. Isue a following update:

UPDATE col$ SET name = 'B'
WHERE obj# = (SELECT obj# FROM obj$

              WHERE name = 'TEST_TABLE'
              AND owner# = (SELECT user# FROM user$
                            WHERE name = 'SCOTT')
             )

AND name = 'A';

4. Restart the database

Again, you are doing this on your own risk (I've used it successfully a couple of times).

>Tim Hall, Indus International (was TSW International)
>tim.hall (at) iint.com
>http://www.indusworld.com

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Apr 20 1998 - 11:03:09 CDT