Re: Removing a Column

Joseph Y. Suh wrote:
>
> Hakan Egeli wrote:
> >
> > Murthy S. Patamalla wrote:
> > >
> > > bjonnard_at_ix.netcom.com (Bill Jonnard) wrote:
> > > >This question has been bugging me for the last few years:
> > > >
> > > >Is there any way to remove a column from a table other than to
> > > >recreate the table (minus the offending column) and then inserting all
> > > >the rows into the new table?
> > >
> > > 1.I do not understand why can not do like
> > > CREATE TABLE(new table) xxxxx AS SELECT 1,2,3,... from TABLE(original
> > > table)
> > > This way the table is created as well as populated with data.
> > >
> > > >
> > > >I am attempting to remove a number of obsolete columns from a very
> > > >large table, and the prospect of having to solve this problem in the
> > > >manner described above almost makes it a pointless exercise due to the
> > > >amount of time, resources, etc that it would take.
> > >
> > > 2.I agree with you some extent if you are doing this for large number of
> > > tables but removing many columns from a single large table, this
> > > actually is the fastest and easiest way.
> > >
> > > >
> > > >Thanks for any advice. -- Bill bjonnard_at_ix.netcom.com
> > > >
> > > >
> > >
> > > OK, if I was of any help to you.
> > >
> > > Murthy
> >
> > What if the 'original table' is referenced by (many) other tables and/or the
> > original table references other tables? Then you are not only going to
> > create a new table but (if you dont have the scripts) you will have to figure
> > out the foreign keys and re-do them as well (definetely takes more time then
> > simply saying 'ALTER TABLE table_name DROP column_name').
>
> How can you drop a column from the table. Is this new feature of
> Oracle7?
> "ALTER TABLE table_name DROP column_name;". You may drop constraints but
> not columns.
> "ALTER TABLE DROP CONSTRAINT constraint_name;"
> I NEVER have heard nor known that you can use ALTER TABLE command to
> DROP COLUMNS. If I were wrong, please advise me.
>
> Joseph Y. Suh
> Oracle DBA
> DB Technology, Inc.
> Atlanta, GA
> 770-352-3175
>
> > Also, depending on the storage parameters, you may copy this table to multiple
> > extents instead of one extent (which you should try to place your table all into one
> > extent).
> >
> > Hakan Egeli
> > hegeli_at_ix.netcom.com

I am sorry if I caused a confusion, there is no such command! That was my "I wish it existed" commands. I was trying to make a point that a command such as  "ALTER TABLE table_name DROP column_name;" would be very simple to use since creating a new table without the column that you want to drop based on an SQL statement may not be an immediate solution because you may have to worry about constraints, segments etc...

Hakan Egeli
hegeli_at_ix.netcom.com
solution! Received on Mon Aug 26 1996 - 00:00:00 CEST