http://www.xifenfei.com/2015/12/type%E4%B8%BAtemporaryname%E4%B8%BAfile-block%E5%AF%B9%E8%B1%A1%E9%87%8D%E7%8E%B0%E5%92%8C%E6%B8%85%E7%90%86.html
跟他里边描述的不太一样,按他写的方法无法进行清理。
继续查发现一篇文章介绍的情况跟我的比较相似,http://blog.chinaunix.net/uid-22948773-id-3758510.html
重启数据库或用下面的方法清理:
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.
1、查询所在表空间的编号SQL> select ts# from v$tablespace where name=‘E2TEST‘;
TS#
----------
5
2、使用上述方法清理SQL> alter session set events ‘immediate trace name DROP_SEGMENTS level 6‘;
Session altered.
3、再次查看最大的TEMPORY段已经没有了,表空间也彻底被释放了SQL> select * from (select owner,segment_name,segment_type,sum(bytes)/1024/1024 from dba_segments where tablespace_name=‘E2TEST‘ group by owner,segment_name,segment_type order by 4 desc ) where rownum
OWNER SEGMENT_NAME SEGMENT_TYPE SUM(BYTES)/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------ --------------------
E2TEST T_CONT TABLE 12332
E2TEST SYS_LOB0000017851C00008$$ LOBSEGMENT 9834
E2TEST ERMSGLOG TABLE 5974
TABLESPACE_NAME SIZE_G FREE_G USED_PCT
------------------------------ --------------- --------------- ----------
E2TEST 96.00 31.68 67.00%
本文出自 “DBA Fighting!” 博客,请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1877451
Oracle 手工清理临时段
标签:oracle temporary
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源:http://hbxztc.blog.51cto.com/1587495/1877451