Re: Oracle Help: Changing Column Names

In article <5cam8bINNkh7_at_retriever.cs.umbc.edu>, jmessi1_at_cs.umbc.edu says...

> I'm currently looking for a method to change column
>names in a table without effecting the data already in the
>table. I'm currently using Oracle version 7 and I'm stumped.
>
> I've tried using Alter Table, but altering a
>column name does not appear to be an option (changing
>datatypes, constraints, etc. yes, column names no).

Oracle doesn't support Alter Table to change column names; Rdb does, and there may be a few others.

The most straightforward approach to this is:

**Warning: back up your table first. If you are just doing one table, I'd be inclined to export it somewhere.

CREATE TABLE NEW_TABLE
(NEW_COL_A, NEWCOL_B, NEWCOL_C)
AS
SELECT COL_A, COL_B, COL_C
FROM OLD_TABLE; See the description of "CREATE TABLE AS subquery" on p. 4-211 (or thereabouts) of the Oracle SQL Language Reference Manual (at least, that's where I found it in my copy dated Dec 1992).

Now you have two copies of the table, one with new column names and one with old. You can drop the old table:

DROP TABLE OLD_TABLE; and then transfer the data back into the original table name with the new column names:

CREATE TABLE OLD_TABLE
AS
SELECT * FROM NEW_TABLE; and finally get rid of the intermediate table:

DROP TABLE NEW_TABLE; The main problems with this approach are:

1. Speed and size. If the table is really large, this approach takes a while, and uses up a big chunk of space. You may wish to assign NEW_TABLE to a temporary tablespace of some sort (insert the TABLESPACE tablespace-name clause right before the AS SELECT clause).
2. Cascading. If the table has a lot of metadata associated with it (indexes, tablespace assignment, etc., etc.) you will lose some of that information. It would be best to have a script available which creates OLD_TABLE with all its adjunct stuff, and to re-run it (without the CREATE TABLE, natch) after the above process.

> I've also tried duplicating the table, copying
>over the data and then droping the original table.
>Unfortunately, I can not drop the original table since
>I have several tables who have foreign keys which originate
>in the table I'm currrently trying to drop.

Yep, that's the catch. Try

DROP TABLE OLD_TABLE CASCADE CONSTRAINTS; Hopefully you have a SQL script to recreate these afterwards. See also the notes in the SQL Lang. Ref. Man. for DROP TABLE about procedures/functions/packages becoming invalid after a DROP TABLE.

--
Jeffery Boes
jboes_at_qtm.net
DBA/Developer/Web Engineer for hire
"Sucking up bandwidth since 1994."