Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, May 29, 2008

Force cleanup of temporary segments through DROP_SEGMENT event

While searching on information about segments, I found metalink note 47400.1 which describes DROP_SEGMENT event:

The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
Available from 8.0 onwards.

DESCRIPTION
Finds all the temporary segments in a tablespace which are not currently locked and drops them.

For the purpose of this event a "temp" segment is defined as a segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY tablespace does not qualify under this definition as such space is managed independently of SEG$ entries.

PARAMETERS
level - tablespace number+1. If the value is 2147483647 then
temp segments in ALL tablespaces are dropped, otherwise, only
segments in a tablespace whose number is equal to the LEVEL
specification are dropped.

NOTES
This routine does what SMON does in the background, i.e. drops
temporary segments. It is provided as a manual intervention tool which
the user may invoke if SMON misses the post and does not get to
clean the temp segments for another 2 hours. We do not know whether
missed post is a real possibility or more theoretical situation, so
we provide this event as an insurance against SMON misbehaviour.

Under normal operation there is no need to use this event.

It may be a good idea to
alter tablespace coalesce;
after dropping lots of extents to tidy things up.

*SQL Session (if you can connect to the database):
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

The TS# can be obtained from v$tablespace view:
select ts# from v$tablespace where name = '';

Or from SYS.TS$:

select ts# from sys.ts$ where name = '' and online$ != 3;

If ts# is 5, an example of dropping the temporary segments in that tablespace
would be:

alter session set events 'immediate trace name DROP_SEGMENTS level 6'

No comments: