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

10 pts.
Oracle Table
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: 2  Replies

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.
  • Rosell
    Below the two scripts to release free space from the datafiles residing on ASM as well as disk. It will work for Oracle 10g, 11g, 12c ..etc.
    10 pointsBadges:
  • ToddN2000
    @Rosell: Thx for passing the link to those scripts along.
    134,720 pointsBadges:

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.

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


Share this item with your network: