Re: Change Field Name - Urgent!

No. It's much more complicated than you think. Even though the latest Oracle version gives you drop column (as many DBAs have expected for years), it still doesn't allow you to alter table rename column.

If your table has no constraint or other dependants, you can simply: create table mytabbak as select * from mytab; drop table mytab;
create table mytab [with new column definitions]; insert into mytab select * from mytabbak; drop table mytabbak;

If your table has constraints, you need to find all those constraints by select * from user_constraints where table_name = 'MYTAB' and do the same to user_cons_columns. Then select * from user_constraints where r_constraint_name = '<the primary/unique key constraint name for MYTAB>'. Find other dependent objects such as triggers possibly from user_dependencies (you probably don't have to look at this view since you may remember those triggers, if any at all). If you have TOAD, all this is easier.

Then do what I say in the 2nd paragraph. Then alter table mytab add constraint one by one. Create triggers. Find all invalid objects and compile them: select 'alter ' || object_type || ' ' || object_name || ' compile;' from user_objects where status = 'INVALID'.

Finally you're done.

Oracle recommends you simply create a view with the correct column name based on mytab. It's much easier but has its weak points. For example, if you create views on this view, performance may be a problem. Besides, you don't feel comfortable with this new view added simply because you made a mistake in column names.

Yong Huang
yhuang_at_indigopool.com

<jdonalds23_at_my-deja.com> wrote in message news:8mc39a$tap$1_at_nnrp1.deja.com...
> I'm trying to change the name of a field with in a table. I currently
> have data in those tables. I'm assuming it's an alter table command
> but I don't know the syntax, please help! I'm using SQL plus to
> manipulate my structure. Also, this is oracle 8i and the field name
> that I'm trying to change is a required field (not null).
>
> Jeremy
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Aug 03 2000 - 00:00:00 CDT