Re: Removing a Column

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
  Received on Sun Aug 25 1996 - 00:00:00 CEST