Thus, a lot of blocks have 75-100% free space but the table constantly growing: during last 9 days the size increased from 123 to 157 Gb.
Size of blocks with: 0-25% free space: 4726784 25-50% free space: 17301504 50-75% free space: 24920064 75-100% free space: 102418669568 full blocks: 54761594880
Please advise how to stop the table growing? It there any way to limit the table size in locally managed tablespace?1.Fix user quota for the tablespace
I just would like to use the free space within the occupied extentsExtents in a segment are "used" -- i.e. there are never "free" extents in a segment.
jgarry wrote:Thank you very much, Joel. I think you're right. The regular (every 10 minutes) deletion from this table is performed by 100K rows portions. Insertions is made continuously, i.e. the deletions and the insertions are simultaneous.
It "sounds like" you are having an issue with adding rows while an uncommitted huge delete is happening, temporarily confusing the decision about which blocks are free. See here for an example: http://jonathanlewis.wordpress.com/2011/01/19/assm-again/
Normally you don't want to commit until a unit of work is done, but if you research and find the above is what is happening, you might want to break the delete into more transactions (for a temporary fix). Your situation might be worsening simply because you have more bitmap size, if that is the case you'd want to shrink, too.Thank you, I'll try to split the deletions into smaller portions. I've used the shrink space for this table a month ago, but I've faced with the complete lock of table during 53 minutes (!) what is unacceptable, so I wouldn't use the same procedure again.