Home » SQL & PL/SQL » SQL & PL/SQL » Dropping unused columns in a partition
Dropping unused columns in a partition [message #41659] Tue, 21 January 2003 11:06 Go to next message
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 Go to previous messageGo to next message
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 #567505 is a reply to message #41675] Tue, 02 October 2012 17:17 Go to previous messageGo to next message
nmuthumalla
Messages: 2
Registered: September 2012
Junior Member
Hi,

3) alter table <your_table> MOVE;


As of now, i am using checkpoint to drop unused column. Kindly let me know whether the mentioned step is tablespace movement?

Also i am working on non partition table.


Thanks
Muthu

[Updated on: Tue, 02 October 2012 17:18]

Report message to a moderator

Re: Dropping unused columns in a partition [message #567514 is a reply to message #567505] Wed, 03 October 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you mean by "tablespace movement"?

Regards
Michel
Re: Dropping unused columns in a partition [message #567524 is a reply to message #567514] Wed, 03 October 2012 02:10 Go to previous messageGo to next message
s.m.ramachandran
Messages: 13
Registered: September 2012
Junior Member
Hi,

I mean moving table from one tablespace to another tablespace. I mentioned below statement

Alter Table <Table_Name> Move Tablespace <Tablepsace_Name>

Regards
Muthu
Re: Dropping unused columns in a partition [message #567527 is a reply to message #567524] Wed, 03 October 2012 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is this "smramachandran" that answers a question I asked to "nmuthumalla"? Are you a (remote) mind reader "smramachandran"?

Regards
Michel
Re: Dropping unused columns in a partition [message #567535 is a reply to message #567527] Wed, 03 October 2012 03:00 Go to previous message
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
Previous Topic: Writing multiple Files using UTL_FILE
Next Topic: UNABLE TO EXECUTE PROCEDURE.
Goto Forum:
  


Current Time: Mon Oct 20 16:56:23 CDT 2014

Total time taken to generate the page: 0.10237 seconds