Re: tempspace usage
> If the problem is the space doesn't disappear from V$SORT_USAGE
> across multiple statements, I'd bet it was temp tables/indexes
> because a sort or hash should disappear when the statement ends
> while the temp table persists until the end of the transaction or session
> depending on how its scope was declared.
Yes, it definately appears to be persisting across individual
statements. Unfortunately this database has no global temporary
tables (aside from SYS.ATEMPTAB$, of course), so that isn't it. :-(
> Also check the SEGTYPE column to see if the space is being used for
> sort, hash or temporary tables/indexes.
Thanx, this was a great suggestion. It seems that all of the
problematic sessions are split between LOB_DATA and LOB_INDEX, with
the former taking the majority of the space. I'm guessing this means
that the application is using temporary LOBS, and not releasing them
once they're no longer needed... I'll check with the developers.
"I'm too sexy for my code." - Awk Sed Fred.