Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to delete a column from a table?
Nevin Hahn wrote:
> Most importantly is to identify the constraints and to know what they are
> doing. You have to actually drop the constraints, disabling them doesn't
> work. So you have to recreate the constraints after the table is
> recreated. Once you're clear on the constraints then do a:
>
> drop table original_table cascade constraints
>
> I would create the table again with proper sizing and then insert the
> records into the new table with:
>
> insert into new_table
> select * from temp_table
>
> Now recreate the constraints with:
>
> alter table new_table
> add constraint ...
That is "the right way of doing it".
I've done it few times. Watch out for time&resources when working with big tables.
I've tested (but not used) another quick-and-somewhat-dirty solution:
After this you won't want to show your schema to a real C.J. Date fan...