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):
- 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.
- 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.
- 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.
- Stored Procedures, Functions, Packages, Views: Will get invalidated
by this process if they refer to the original table. You will want to
recompile them.
- 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