Can you rename a column?

[jt]

Hi, This might be to obvious to some of you, but how, or if it is possible to rename an existing column with data already in it?

Thanks.

[vidhya]

There is no command to drop columns until Oracle 8.1.

The best way to do this in ver 7 - 8 is probably to rename the existing table. Then re-create the table looking the way you want followed by inserting the data using an insert as select statement.

create table NEW_TABLE (column name datatype) AS SELECT * FROM THE OLD_TABLE.

vidhya

[omegamark]

The answer is yes and no.

Yes means you can do in a round about manner like creating a view.

create table test (n number);
rename test to test123456789;

create view test (m) as select n from test123456789;

through view you can mask without really affecting the table or data's.

otherwise you have drop and recreate.

hope this helps

[aracnid]

take a look at this example:
create table klf (name varchar2(10), age number, sex varchar2(1));
insert into klf values ('keith',38,'M');
insert into klf values ('sue',39,'F');Suppose that we wanted to rename column 'n
ame' to 'person'. The first step is to identify the object number for the table (as SYS)
:
select obj# from obj$
where name='KLF'
and owner#=9;(get the owner id from dba_users)

OBJ#
61341 Now identify the column numbers for the table:
select obj#, col#, name from col$
where obj#=61341;

OBJ#COL#NAME
613411NAME
613412AGE
613413SEX To change the name, a simple update statement will suffice:
update col$ set name='PERSON'
where obj#=61341
and col#=1;
To reflect the change in system views, you should now run catalog and catproc (un
ix) eg:
svrmgr> @$ORACLE_HOME/rdbms/admin/catalog
svrmgr> @$ORACLE_HOME/rdbms/admin/catproc Now when you describe the table:
desc klf
NameNull ?Type
-----------------------
PERSON VARCHAR2(10)
AGE NUMBER
SEX VARCHAR2(1) Similarly, selecting