手工清理临时段

由于表空间剩余空间紧张，同事删除了一个大表，以便腾出点空间。在删除的过程中HANG住了，然后就手工停掉了。
最终发现这个表所在的SEGMENT并没有删掉，而被重名为数字，并且SEGMENT_TYPE成了'TEMPORARY'。
理论上到此表其实被删除了，相应的段先变成临时段，交由SMON进程去处理。
查询DBA_SEGMENTS和DBA_EXTENTS，有3个临时段和1027个临时EXTENT。

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='CUST' AND SEGMENT_TYPE='TEMPORARY';

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
1470.550035          CUST_DATA
1467.518419          CUST_DATA
1452.112843          CUST_DATA

SQL> SELECT COUNT(*)
  2  FROM DBA_EXTENTS 
  3  WHERE SEGMENT_TYPE = 'TEMPORARY';

  COUNT(*)
----------
      1027
      
这个问题可以通过重启数据库来解决，但是很多时候数据库并不允许我们去重启。

后来朋友介绍了一种方法来手工删除临时段:

alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';

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.



SQL> select ts# from v$tablespace where name='CUST_DATA';

       TS#
----------
       291
       
SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 292';

Session altered.

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='CUST' AND SEGMENT_TYPE='TEMPORARY';

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
1452.112843          CUST_DATA

SQL> SELECT COUNT(*)
  2  FROM DBA_EXTENTS 
  3  WHERE SEGMENT_TYPE = 'TEMPORARY';

  COUNT(*)
----------
       790

应用事件之后，删掉了2个临时段，最后一个临时段竟然没有删掉。



SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 292';

Session altered.

SQL> SELECT COUNT(*)
  2  FROM DBA_EXTENTS 
  3  WHERE SEGMENT_TYPE = 'TEMPORARY';

  COUNT(*)
----------
       790

SQL> /

  COUNT(*)
----------
       790

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 2147483647';

Session altered.

SQL> SELECT COUNT(*)
  2  FROM DBA_EXTENTS 
  3  WHERE SEGMENT_TYPE = 'TEMPORARY';

  COUNT(*)
----------
       790
       
       
 尝试了好几次，始终不能删掉，本该放弃了，等了10几分钟后，发现自动清理了。
 
 
 
SQL> SELECT COUNT(*)
  2  FROM DBA_EXTENTS 
  3  WHERE SEGMENT_TYPE = 'TEMPORARY';

  COUNT(*)
----------
       790

SQL> 
SQL> /

  COUNT(*)
----------
         0

SQL> 

省去重启数据库了^-^。  同时发现SMON posted for dropping temp segment 增加了1。