Extent allocation time -- process check

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Thu, 4 Feb 2010 13:23:16 -0600 (CST)
Message-ID: <7773e08356ebc21d6657b2af2edd7343.squirrel_at_society.servebeer.com>

Hey all,

We had an unexpected filling of a production tablespace in Now, after the triage, I'm trying to determine how that could have happened. Here's the facts:

  • Single instance (non-RAC)
  • TS is locally managed and AUTOALLOCATE (sigh)
  • 7 datafiles totalling ~214GB
  • Lovely Grid Control (LGC) reported shortly before the incident that the TS was 95.66% full, which leaves ~9GB unallocated.
  • LGC has the TS full metric collecting every 30 minutes.
  • Consistent growth over the past three years has been 11GB per *month*.

The 9GB growth spike in a matter of minutes is highly suspect. My theory is that LGC is full of S. Either LCG's wrong or there is significant waste due to extent fragmentation, which in my case is much less likely than the former as segments in this TS are rarely dropped (the DBA_FREE_SPACE view would seem to agree with this).

So I've restored the pertinent archive logs plus and minus two hours to view the extent allocations as specified in this thread:


In order to pull the object name from the opcode 14.4 trace, I've taken the DBA hex value from the "ADD:" line and used it in this SQL:


	file_id = DBMS_UTILITY.data_block_address_file (TO_NUMBER ('a3bc189',
	AND block_id = DBMS_UTILITY.data_block_address_block (TO_NUMBER ('a3bc189',

...with 'a3bc189' being the DBA hex value from the trace file. Repeat for each value found in the trace.

Is this the (or 'a') correct method for determining what extents were allocated in a given time period? Everything appears to tie out for me, but I can't find any verification of the method I'm using.



Received on Thu Feb 04 2010 - 13:23:16 CST

Original text of this message