Extent allocation time -- process check
Date: Thu, 4 Feb 2010 13:23:16 -0600 (CST)
We had an unexpected filling of a production tablespace in 10.1.0.5.0. 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:
'a3bc189', file_id, tablespace_name, owner, segment_name, block_id FROM dba_extents WHERE file_id = DBMS_UTILITY.data_block_address_file (TO_NUMBER ('a3bc189', 'xxxxxxxx')) AND block_id = DBMS_UTILITY.data_block_address_block (TO_NUMBER ('a3bc189','xxxxxxxx'));
...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.