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.
Printable View
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.
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
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
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