DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments

=== ODM Action Plan ===
안녕하세요.

일반 PERMANENT TABLESPACE 에 과거에 생성된 TEMPORARY SEGMENT가 정리되어야 하는데 정리되지 못한 상태로 남아 있는것으로 보여집니다.

운영 및 재배치에는 문제가 없습니다만, 재배치 이전에 정리를 하고 싶으시면 아래 문서를 참고 하셔서 해당 Tablespace에 있는 TEMP SEGMENT를 삭제 하실 수 있습니다.

EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments (Doc ID 47400.1)
-----------------------------------------------------------------------------------------------------------
위 문서를 요약하면 아래와 같습니다.

select ts# from v$tablespace where name = '<Tablespace name>';

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';

노트를 한번 읽어보신 후에 위 command로 정리 작업을 해주시기 바랍니다.
====================================================================================
EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments [ID 47400.1]

--------------------------------------------------------------------------------
 
  수정 날짜 22-OCT-2010     유형 REFERENCE     상태 PUBLISHED  

 

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 <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 = '<Tablespace name>';

     Or from SYS.TS$:

     select ts# from sys.ts$ where name = '<Tablespace 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';