More importantly, it drops all of the constraints first so that you don't
get any errors when you drop the table. It then recreates the
constraints at the end so that everything is back the way it was.
In article <385FEFD2.15FF8203_at_us.oracle.com>, psharman_at_us.oracle.com
says...
> There are a variety of tools that do something similar to DB Artisan. All the
> ones I know of do exactly the same thing:
>
> 1. Suck out the DDL to recreate the table (without the column) and dependent
> objects (indexes, grants, constraints etc.)
> 2. Suck out the data in some way (fast unload, export etc.)
> 3. Drop the table.
> 4. Recreate the table.
> 5. Reload the data.
> 6. Recreate the dependent objects.
>
> Pete
>
> Brad wrote:
>
> > In article <385B754F.1E91_at_yahoo.com>, connor_mcdonald_at_yahoo.com says...
> > > 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...
> >
> > This could create a huge mess in your database over time. DB Artisan
> > does this kind of stuff for you and cleans up its mess.
Received on Wed Dec 22 1999 - 07:36:56 CST
