Re: temorary segments

Richard Ramirez <ou79138_at_deere.com> wrote:

>chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:
>>I'm having problems with a 7.1.3 database not cleaning up TEMPORARY
>>segments after dropping tables. What can I do short of bouncing the
>>database to make it clean them up?
>>
>>The process I'm running drops all snapshots in a schema and recreates
>>them (much faster than doing a complete refresh) but the tablespace
>>runs out of space because the temp segments are constantly hanging
>>around.
>>--
 

>Try to create a table in that tablespace. It will release the
>temporary segments.
 

>Richard

That's what's failing because the temporary segments are hanging around.

Let me restate the problem since I got several responses answering the wrong question. Maybe I was unclear in my original article.

This is *not* a problem of free space needing coalescing. The problem is that when I drop a snapshot, rather than having it's space released immediately and placed into the free_space table, it appears to be just converted from a table segment, to a temporary segment. Likewise with the associated index segments. But temp segments still occupy space. They eventually disappear by themselves, but I don't know when or what causes it. The only way I've been able to force it to happen is to bounce the database. I don't want to do that though because it's a SQLPLUS batch job that's trying to drop and recreate everything. Plus, there may be other users on the database at the same time. Bouncing just isn't an option.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!