Changing Column Names

[ronnie]

Hi,

I need to change column names of some tables in the Database.

These Tables have Data,Integrity Constraints and also synonyms.

whats the best way for doing this.

Thanks
Ronnie

[tamilselvan]

Take the constraints definition from the existing tables.

create table newtable(newcol1, newcol2..) as select (col1, col2..) from oldtable;

drop the old table.

Edit constraints.sql and run it again.

[sanjay92]

If you are using Oracle 9i then oracle provides another way called redefinition of table. It is called dbms_redefinition which is also used for the same purpose. The main advantage of using this package is , This is online redefinition and users are not affected.

Sanjay

Originally posted by tamilselvan
Take the constraints definition from the existing tables.

create table newtable(newcol1, newcol2..) as select (col1, col2..) from oldtable;

drop the old table.

Edit constraints.sql and run it again.

[ronnie]

I just found out
This is how you can rename a column without dropping or recreating or doing anything.

Just a straight and simple update

undefine newname
undefine oldname
undefine ownername
undefine tablename
update SYS.COL$ c
set c.NAME = '&&newname'
where c.NAME = '&&oldname'
and c.OBJ# in (select o.OBJ# from SYS.OBJ$ o, SYS.USER$ u
where o.OWNER# = u.USER# and u.NAME = '&&ownername' and
o.NAME = '&&tablename');


btw its not supported by oracle :-)