Re: dropping a column

From: Dan Townsend <townsend_at_ebmud.com>
Date: 1997/07/10
Message-ID: <33C4F4D3.766F@ebmud.com>#1/1

Ceri Moran wrote:
> I have two columns in the middle of a table, I wish to move to the
> end, I cant seem to reorder using the Navigator or SQLPLUS.

Ceri: In relational theory, the order of columns in a table is insignificant. If order is significant, it is incorrect. You can retrieve the columns in any order as needed.

> Please could you advise me the correct SQL for dropping a non-key
> column from a table?

I wish I had time to write a treatise on this. Just creating a temporary table from the current table, dropping the current table, then renaming the temporary table is a dangerous understatement in a complex production environment. Other things you need to be aware of include (no guarantees this is the complete list):

1. Indexes: Your own (and potentially indexes created in other schemas) need to be recreated in the correct tablespaces. If you have index naming conventions, you may need to drop the original indexes before you can create the new ones. If the table is large and has grown over time, you might run into temp space problems creating the new one. If you do, you will have fun because the old one is gone. Be prepared to add 'temporary' temporary tablespace to cover this.
2. Constraints: Foreign Key constraints referencing the table will follow the renamed original table, then get dropped when the table is dropped. They do not magically point to the new table. You need to identify your own, and potentially other schemas', foreign keys and be prepared to rebuild them. Of course, you will need to also preserve the Primary Key constraint, plus any Unique and Check constraints on the table.
3. Triggers: If the original table has triggers, you will need to add them to the new table. Best to do this after the new table has been populated with data.
4. Stored Procedures, Functions, Packages, Views: Will get invalidated by this process if they refer to the original table. You will want to recompile them.
5. Replication: If the table is being replicated, the replication will need to be rebuilt for the new table.

With the power of Oracle comes complexity. Knowledge and Preparation are the key to success.

+-----------------------------------------------------
| Dan Townsend, Supervising Database Architect

| EBMUD Enterprise Object Designer
| mailto:townsend_at_ebmud.com

+-----------------------------------------------------

Received on Thu Jul 10 1997 - 00:00:00 CDT