Re: Removing a column from a table?

From: GHouck <hksys_at_teleport.com>
Date: 2000/06/13
Message-ID: <3946DD17.5022@teleport.com>#1/1

Jan Kim wrote:
>
> Jean-Christophe Boggio <cat_at_creaweb.fr> wrote:
> > This is a very FAQ ! You can find solutions everywhere !
 

> > quick way is :
 

> > create temp_table as select <all_wanted_fields> from good_table;
 

> > then erase good_table and rename temp_table to good_table. There are
> > packages that do this and I wonder if Oracle8i doesn't have this
> > implemented... (although it is violating ANSI SQL compliance).
>
> Ok, I admit that this answers the question I posted, but unfortunately,
> it does not solve my problem. Unfortunately, there are quite a few
> foreign keys in other tables referencing the table in question.
>
> So, to be more precise, I look for a way to get rid of a superfluous
> column which does not demand a temporary dissolution of referential
> integrity.
>
> Sorry for having been imprecise before.
>
> Greetinx, Jan
> --
> +- Jan T. Kim -------------------------------------------------------+
> | email: kim_at_mpiz-koeln.mpg.de |
> | WWW: http://www.mpiz-koeln.mpg.de/~kim/ |
> *-----=< hierarchical systems are for files, not for humans >=-----*

Jan,

Here is a script I found for Oracle8i (I cannot vouch for its veracity):

rem

---

rem Filename: dropcol.sql
rem Purpose: Demonstrate Oracle8i drop column functionality

rem Date:       28-Aug-1998
rem Author:     Frank Naude (frank_at_onwe.co.za)

rem

---

drop table x
/
create table x(a date, b date, c date)
/
-- Mark col as UNUSED
alter table x set unused column b
/
select * from sys.dba_unused_col_tabs
/
alter table x drop unused columns
/
alter table x drop column c cascade constraints /
rem

---

Yours,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Tue Jun 13 2000 - 00:00:00 CDT