WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606
全部博文(599)
分类: Oracle
2013-06-06 16:36:01
由于表空间剩余空间紧张,同事删除了一个大表,以便腾出点空间。在删除的过程中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。