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.

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window