| Dropping unused columns in a partition [message #41659] |
Tue, 21 January 2003 11:06  |
Mon
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
Urgent.... I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead? How to apply that? Or, what are my options besides increasing the size of the rollback segment? Please help... Thank you. MON
|
|
|
|
| Re: Dropping unused columns in a partition [message #41675 is a reply to message #41659] |
Wed, 22 January 2003 02:47  |
Adrian Billington
Messages: 139
Registered: December 2002
|
Senior Member |
|
|
Unfortunately you've hit one of the drawbacks of drop column. People see "drop column" being available from 8i onwards and think "oh great" - let's get rid of a few. Until they hit a big table.
You now have no option but to continue. Look up the CHECKPOINT option to DROP COLUMN in the docs. This might help.
Basically, when you drop a column, every record in the table must be re-written to exclude the data formerly held in the unwanted column. This generates undo and redo as you've encountered and is a very expensive operation.
For future reference, if the table is large (as in your case), it would be better to do the following:-
1) alter table <your_table> set column <byebye_column> UNUSED;
2) repeat step 1 for all columns you wish to remove;
3) alter table <your_table> MOVE;
This will rebuild your table without unused columns and will be screamingly faster and more efficient that the alley you've found yourself in.
Good luck.
Regards
Adrian
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Dropping unused columns in a partition [message #567535 is a reply to message #567527] |
Wed, 03 October 2012 03:00 |
 |
nmuthumalla
Messages: 2
Registered: September 2012
|
Junior Member |
|
|
Hi Michel,
Sorry for inconvenience, me and Ram are friends. My laptop got crash, so I used Ram laptop. While replying I didn't notice login details. I just concentrate on clarifications. Accidentally I replied in Ram login.
Hi Ram,
Sorry for inconvenience.
Regards,
Muthu
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
