Re: dropping a column

Atul Ashar wrote:
>
> Hi Ceri,
>
> I cannot think of any reason why you would care for sequence of columns
> in a relational db tables.

Here's a reason, the physical order of the columns can affect the behavior of the database. For instance, if you add a LONG column to a table, then later add some VARCHAR2, DATE, NUMBER columns, your LONG column is no longer at the end of the table - this can cause things to behave badly. I've had a 7.2 database with this situation which would periodically reduce the database to a CRAWL when particular records in this table were accessed. The database would need to be shutdown and brought back up to restore normal function!

> However, if you have to do it then simplest
> solution is rename that table to different name and define view on that
> table with proper sequence of columns.

When I fixed a similar situation, I found it easier to export the table including all data and indexes and grants. Then I exported it again with no rows or indexes which results in basically just the table creation SQL statement going into the export file. I then edited this file and ordered the columns as desired. Then I backed up the containing tablespace (always do this or a full backup when you are dropping tables, people!) then a control file backup, then dropped the table, ran the script to create the fixed version of it, then imported the data back into it. This creates all the indexes for you, the grants, etc.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||