Re: how to drop a column in a table?

Depending on which version of Oracle , certainly 7.2 and above, you could adapt Klaus' answer a little to read

 Create table new_table unrecoverable as

    SELECT col1, col2, ... , col[n-1] -- everything but the one you drop     FROM old_table;

This has an advantage, if the table is large, that the unrecoverable option will ensure no redo information is created and hence no extensive rollback space is needed.

The difference in time taken can be quite startling on large tables.

regards

Rod Corderey

Lane Associates
RodCorderey_at_Lane-Associates.com
http://www.Lane-Associates.com

Klaus Sonnenleiter wrote:
>
> Erin,
>
> Actually, Oracle8i has a DROP COLUMN command. You probably don't want to migrate
> just to be able to drop one column <g>, so your only choice is as Voornaam
> suggested do a
>
> INSERT INTO new_table
> SELECT col1, col2, ... , col[n-1] -- everything but the one you drop
> FROM old_table;
>
> then drop old_table and
>
> RENAME new_table TO old_TABLE;
>
> Hope this helps
>
> Klaus Sonnenleiter
> The Media Machine, LLC
>
> Voornaam Achternaam wrote:
>
> > There is no single command to drop a column. You'd have to unload the date,
> > recreate the table without the column(s) you want to drop and reload the
> > data.
> >
> > Good luck
> >
> > Erin A. O'Neill wrote in message <7jmmor$2i2b$1_at_nnrp9.crl.com>...
> > >I would like to drop just a column in a table. What's the syntax for this?
> > >
> > ...
Received on Tue Jun 15 1999 - 14:33:34 CEST