Re: [Q] How do I drop a column from a table?

N Prabhakar wrote:
>
> logic probe_at_bbs.kis.net (Steve Crowe) wrote:
> >I am trying to drop a column from a table. I thought that I could
 use
> >the ALTER TABLE command, but I can't seem to get it to work. I am
> >using "Oracle: The Complete Reference" by Koch and Loney. It has
 lots
> >of stuff on adding and modifying columns, but not one word on
 deleting
> >or dropping them.
> >
> >Any help would be most welcome,
> >
> >Steve Crowe
> >logicprobe_at_bbs.kis.net
> >
>
> Hi there,
>
> YOU CANNOT DROP A COLUMN IN AN ORACLE Table.
>
> If you want to drop a column, consider the following example
>
> table t1 has got columns c1, c2, c3.
>
> If you want to drop col c2, then
>
> rename table t1 to t1_old;
> create table t1 as select c1, c3 from t1_old;
> drop table t1_old;
>
> The above steps will effectively achieve your objectives.
>
> Regards
>
> N.Prabhakar

Note that it is not quite that simple if you have grants, indexes, or constraints on the original table. You would have to make sure that all that stuff gets rebuilt after the new table is created.

There are a number of tools out there that you can buy to help you with this. Otherwise, you have to manage it yourself.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com