Link to home
Start Free TrialLog in
Avatar of joe_echavarria
joe_echavarriaFlag for Dominican Republic

asked on

Reclaiming space on Tables, Indexes,Lob Index,Lob Partition, Lob Segments

Hi,

  There is a big database and we are having issues with space.     I want to reclaim unused space from  segments.

  a) What query can i perform that shows unused space of segments ?

  b)  I will like to know  how to reclaim unused space from these different type of segments :Tables, Indexes,Lob Index,Lob Partition, Lob Segments.

 c) How to reclaim space from LOB Columns.

Thanks.
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
Sean Stuber

within an object,  yes, you can do that.

but.. that doesn't make your tablespace any smaller, so your database will still be just as big as it was before hand.

To use an analogy, if you have a 1 gallon bucket full of water and then you pour some of the water out, the water had "shrunk" but the bucket is still the same size.

But continuing the analogy - you now have more space in the bucket to add more water.
Same with a tablespace, if you shrink the objects within it, then you can grow those objects again, or add more objects.  The tablespace is still the same size.
Avatar of joe_echavarria

ASKER

But having the less data in the tablespace might help us to improve some performance ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It "can" help, but usually not by much.

If you have  1000 rows of data spread across 1000 data blocks, then you'll need to read 1000 data blocks to get your data (full table scan or index read, maybe even more with full table scan)

If you shrink your data down to fewer blocks (i.e. more rows per block) then it requires fewer reads.

Full table scans as mentioned above are a particularly bad case because you'll read through empty blocks en route to finding data blocks.  

Index scans are at least guaranteed to point to "some" data", but you still might end up reading more blocks than necessary if those blocks have lots of free space in them.
I've been thinking about your original comment, "issues of space".  While the advice provided is from the best, I wonder if this is addressing your performance concern.  

For example, what evidence has led you to deduce there's a problem.  This could be something like your file systems are full; or there is a rise in I/O wait time; or there are years upon years of obsolete data; or the users perceive that interactive queries are taking longer than usual.

So please say more, if you can .
I have shrinking space and performance have improved a lot.