Re: Howto change/delete a column name without drop table?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 07 Feb 1999 22:07:34 GMT
Message-ID: <36d20dbd.21263765@192.86.155.100>

A copy of this was sent to Albert Zhu <lzhu_at_mail.gte.net> (if that email address didn't require changing) On Sun, 07 Feb 1999 11:31:27 -0500, you wrote:

>Hi,
>
>I am looking for a way in sqlplus to change/delete a column name. The
>ALTER TABLE seems can not do that. And I can not drop the table
>because it is tied to too many other tables.
>
>Any help is very appreciated.
>
>Albert

some modifications can be made (see alter table modify column) but renaming or dropping a column is not one of them (until Oracle8i anyway).

Currently, the fastest way to do this is

• make the column nullable if it is not already
• rename the table.
• create a view of the table that does not select the column if you want to drop it or renames the column if you want to rename it.

using the view is analogous to the new 8.1 feature "marking a column unused" without having to use a view.

In Oracle8i, you can

• make columns unused. like a flag delete, they disappear from the data dictionary but existing space on blocks is not reclaimed. very fast regardless of the size of the table.
• drop a column. reclaims space for a given column or for all columns previously marked unused. takes as long as the table is big to execute, reclaims the space.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Feb 07 1999 - 16:07:34 CST