Re: SECUREFILES disaster

From: Jonathan Lewis <>
Date: Sun, 29 Apr 2012 11:03:30 +0100
Message-ID: <>


Jonathan Lewis
Oracle Core (Apress 2011)

"Mladen Gogala" <> wrote in message

| Jonathan, I am not aware of any free space regulating mechanisms within a
| LOB segment. Within the table segment, there are free lists or the lists
| of blocks with 25%,50% and 75% of used space, index segments have their
| own mechanism, but no such things for LOB segments.
I think this is a refernce back to my comment about extent-level contention - and you're right, i was thinking of bitmap space allocation when I wrote it, but the LOB space allocation is managed by the lobindex, of course.
| Mathias Hoys has recently posted a thread about less then 7000 rows
| consuming 900MB. It was an Apex table and the culprit was LOB.
I've added a note to that thread - the final 200MN size for 7,000 rows is potentially perfectly reasonable. The 900MB extreme after a 4 year life cycle could also be explained with a fairly small (relatively speaking) variation in usage.
| I had a
| close encounter with LOGMNR_SPILL$ table, which had its LOB column
| SPILL_DATA grow over 32GB. The version was, 64bit. The database
| itself is a logical standby used for reporting purposes, LogMiner cannot
| be avoided. I had to run "SHRINK SPACE COMPACT CASCADE" on that table.
| The table is owned by the user SYSTEM and is located in SYSAUX
| I have been firefighting LOB segments explosion for the last 5 years.
If that's spill data in the standby isn't is populated when the incoming logical change records can't be applied fast enough to keep up with the source - and if that's the case, why would any extreme size be a surprise ? 32GB at 16KB per chunk is only 2M chunks; that might only be a few hours of overload to grow, and just like any HWM it just doesn't shrink. Regards Jonathan Lewis
Received on Sun Apr 29 2012 - 05:03:30 CDT

Original text of this message