Donald K. Burleson Oracle Tips

Re-naming a column within an Oracle table

Oracle does not provide an easy facility for re-naming a table column, but there is a sequence of Oracle commands that can be used to re-name a column.

Assume we have the following table

Flight_crew

(   pilot_name      char(40),

    co_pilot_name   char(40)

    stewardess_name char(40)

);

Being politically correct, we must re-name the stewardess_name  to flight_attendant_name.

The procedure is to re-name the table, copy the columns (with new names) from the table, and then re-name the tables.  Of course, you must re-apply all indexes and referential integrity constraints.

rename flight_crew to flight_crew_old;

create table

   flight_crew_new

(   pilot_name            char(40),

    co_pilot_name         char(40)

    flight_attendant_name char(40)

)

as

   select *

from

   flight_crew_old;

drop table flight_crew;

rename flight_crew_new to flight_crew;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: