Re: Changing a column

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/04/21
Message-ID: <353cb44a.30454413@www.sigov.si>#1/1

On 21 Apr 1998 06:46:39 GMT, "Sbaiz" <pierluigi.sbaiz_at_elsag.it> wrote:

>Hi to all
>is there a way, in Oracle, to modifiy a column name without dropping the
>table
>maintaining its data. I know that others databases permit changing the name
>of
>the column or dropping a column, but Oracle not.
>It would be nice if Oracle can modify the column name or generate the
>scripts
>by itself, taking care of all the constraints.

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. Shutdown and restart the database

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

>Thanks in advance.
>Pierluigi S.

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 Tue Apr 21 1998 - 00:00:00 CDT