Re: Removing a Column

In article <4vbi2b$73s_at_sjx-ixn2.ix.netcom.com>, 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?

Well, it's easy to use CREATE TABLE AS... while deleting the columns no longer needed--however, and applications which already use the tables in their "old" structure may break.

There may have been a better way to do this, but when I needed to change one of my columns from VARCHAR2 to DATE, I created table2, ran a simple PL/SQL script to insert rows of table1 to table2, using TO_DATE to populate table2, and then deleting table1 and finally invoking "CREATE TABLE table1 AS (SELECT * from table2)" to finish the transformation. Finally, I deleted table2. Of course, this table only has about 400 rows. I wouldn't recommend this for, say, 400,000 rows unless you have a lot of disk and a lot of time to keep table1 unavailable.

> 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.

Do you have enough disk to do this by creating another table before deleting the old one?

Depending on how much you're troubled by the obsolete columns, you could just create views which mask out the obsolete columns. But that is probably best left as a short-term solution.

-- 
Tim Irvin, HP-UX System Administrator / Oracle DBA
Lockheed Martin Missiles and Space, Sunnyvale, California
e-mail: irvin_at_lmsc.lockheed.com    voice:  (408) 742-0440
************ all standard disclaimers apply ************