Re: Drop column package?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Sat, 18 Dec 1999 19:51:43 +0800
Message-ID: <385B754F.1E91@yahoo.com>

cc wrote:
>
> kal121_at_yahoo.com wrote:
> >
> > Oracle 8i allows column drops (but not previous versions):
> I know that.
> >
> > ALTER TABLE my_table DROP COLUMN col1;
> > Make sure you have your COMPATIBLE parameter set to 8.1.0.
> >
> > If you are not running 8i, you can simply recreate the table without
> > the column, if you can afford the downtime.
> What about all the constraints and the foreign keys from and to a
> table?
> >
> > In article <385A558D.F6C3E0BB_at_intrasoft.gr>,
> > CC <kkons_at_intrasoft.gr> wrote:
> > > hi all,
> > >
> > > Does anyone know where can I find a package or a tool that can drop a
> > > column of a table?
> > > I already got one but it seems not to work.
> > > I 've heard about Hora-4 tool but I can't download it and it is only a
> > > trial version.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

An easier solution for pre-8i:

alter table XXX modify col_to_go null;
rename table XXX to XXX_ORIG;

create or replace view XXX as
select ...
from XXX_ORIG;

(where ... is the columns minus the one you want to drop)

issue the current grants (dba_tab_privs) for XXX for the new view...

Voila!
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Sat Dec 18 1999 - 05:51:43 CST