Re: Renaming columns

bchow_at_rogers.com (Bob Chow) writes:
>Hi everyone,
>Does anybody know of another quick way to change the column names for an
>Oracle table without having to drop it first .
>Thanks.
>Bob

Newsgroups: comp.databases.oracle
Subject: Re: Renaming a column.
References: <WOLKOWSB.93Oct27230255_at_hp.hp> Reply-To: andy_at_vistachrome.com

wolkowsb_at_hp.hp (Brian Wolkowski 263-2600) writes:

>In a previous newsletter is was asked how one could rename a column in
>an ORACLE table. At the time i never gave the matter much thought.

[2 good and recommended methods deleted]

There is another alternative, and it has to do with playing around inside the data dictionary of the Oracle instance in question. The following method works under V6, and it probably works under V7. Needless to say, I won't be responsible for any use of this information.

First, use sqldba to shutdown and restart your database in EXCLUSIVE mode.

cyan% sqldba shutdown
cyan% sqldba command="startup dba"

Now connect to the oracle instance with sql*dba as the SYS user.

cyan% sqldba sys/change_on_install_password SQLDBA> First find the user ID of the owner of the table you want to rename a column within.

SQLDBA> SELECT USER#, NAME FROM USER$ WHERE NAME='owner-name';

Record the USER# for later use.

Find the object number of the table for the column you want to rename. This is uniquely identified by OWNER# & NAME in the OBJ$ table.

SQLDBA> SELECT OBJ#, OWNER#, NAME FROM OBJ$ WHERE OWNER#={owner}

     2> AND NAME='table-name';

Record the OBJ# for later use.

Now you find the column number for the column you wish to rename. Using the OBJ# returned above, find the column in the COL$ table.

SQLDBA> SELECT OBJ#, COL#, NAME FROM COL$ WHERE OBJ#={object}

     2> AND NAME='column-name';

You now have the OBJ# and COL# necessary to rename the column. Update the column name to set it to what you wish.

SQLDBA> UPDATE COL$
     2> SET NAME='new-column-name'
     3> WHERE OBJ#={object} AND COL#={column}
     4> ;

Now commit your change.

SQLDBA> COMMIT; Disconnect from the database, SHUT it down, and restart it in User mode. This does the most important step: flushing of the dictionary cache.

SQLDBA> DISCONNECT
SQLDBA> SHUTDOWN
SQLDBA> STARTUP
SQLDBA> EXIT

>Comments ?

Be careful. Make no typographical errors. Back up your database before beginning.

>--
>Later...
>Brian R. Wolkowski
> e-mail: wolkowsb_at_cuugnet.cuug.ab.ca
> phone: (403) 242-7886
 

-- 
Andrew Finkenstadt  |  Systems Analyst, Homes & Land Publishing Corporation
+1 904-575-0189     |  GEnie Sysop             ,,,
andy_at_genie.geis.com |                         (. .)     Peek-a-boo!
andy_at_homes.com      +----------------------o00-(_)-00o---------------------