How Do I release unused space from a tablespace after dropping a blob table

10 pts.
Tags:
BLOB
Oracle Tablespaces
I have a 30 GB BLOB table in a 162 GB tablespace. After I used the online table reorg to move this BLOB table and its BLOB index to another tablespace, Toad tells me that I have about 35 GB free. However, I can not reduce the size of this tablespace (i.e, resize the datafiles associated with this tablespace). Question: Is there a way to reduce the size of a tablespace after we drop a big table in that tablespace ?

Answer Wiki

Thanks. We'll let you know when a new response is added.

No i do not think you can. There is no easy way of doing this that is. You can move all of your data out of the tablespace and then drop the tablespace, recreate the table space and then move all of our data back into that tablespace. Oracle does not give an easy way to shrink the tablespace or datafiles that are associated with that tablespace. I learned this the hard way but I was working in perfstat so it was not such a big deal and I just truncated the tablespace removed and recreated it. I would not truncate the data in the scenario that you are talking about. You may ask yourself if 35Gigs is worth all the trouble, downtime, and room for error. 35G is not that much disk to waste.

Good Luck

The only space that can be easily reclaimed is at the “end” of each datafile in the tablespace. This would be determined by looking at the dba_extents table, specifically the block_id and blocks columns for each file_id of the tablespace. Multiplying max(block_id + blocks – 1) by the ‘db block size’ for each file in the tablespace will tell you where the max “table occupied block” is in the datafile. You can shrink each individual datafile down to that size. Chances are that the extents for the moved table and index were interspersed throughout the datafile(s) and the space will not easily be reclaimed.

Unless there is a compelling reason to shrink the tablespace, it is probably best left as it is as mentioned above.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following