今天开发说一个测试库里的表空间满了需要清理，连上测试库一看，确实已经使用99.98%了。

1
2
3
TABLESPACE_NAME                SIZE_G          FREE_G          USED_PCT
------------------------------ --------------- --------------- ----------
E2TEST                               96.00            0.02    99.98%

  通过与开发沟通确定了删除策略，先删除了一些历史表但是清理效果不大，直接通过DBA_SEGMENTS视图查看ESOP2TEST表空间中bytes最大的表为E_CONT,占了28G。打算清理直接这张表。

1
2
3
OWNER                          SEGMENT_NAME       
SUM
(BYTES)/1024/1024
------------------------------ ------------------ --------------------
E2TEST                         E_CONT                  28983.3125

  开发要求保留最近一个月的数据，所以打算建一张临时表把最近一个月的数据导进去，删除原表，再把临时表重命名为原表。过程其实很简单，步骤也不复杂，但是在做完全部步骤后发现表空间使用率依然很高，清理没有效果。

1
2
3
TABLESPACE_NAME                SIZE_G          FREE_G          USED_PCT
------------------------------ --------------- --------------- ----------
E2TEST                               96.00            0.89    99.07%

再次查看通过DBA_SEGMENTS查看空间占用情况。

1
2
3
4
5
SQL> 
select 
* 
from 
(
select 
owner,segment_name,
sum
(bytes)/1024/1024 
from 
dba_segments 
where 
tablespace_name=
'E2TEST' 
group 
by 
owner,segment_name 
order 
by 
3 
desc 
) 
where 
rownum < 50;      
 
OWNER                          SEGMENT_NAME                   
SUM
(BYTES)/1024/1024
------------------------------ ------------------------------ --------------------
E2TEST                      20.962                                        28981

segment_name居然变为了数字。。。

查看这个段的类型为TEMPORARY

1
2
3
4
5
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 < 50；
 
OWNER                          SEGMENT_NAME                   SEGMENT_TYPE       
SUM
(BYTES)/1024/1024
------------------------------ ------------------------------ ------------------ --------------------
E2TEST                      20.962                         
TEMPORARY                         
28981

在网上查资料，先看到惜分飞的一篇blog，介绍type为TEMPORARY,name为file#.block#对象重现和清理

跟他里边描述的不太一样，按他写的方法无法进行清理。

继续查发现一篇文章介绍的情况跟我的比较相似，

重启数据库或用下面的方法清理：

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、查询所在表空间的编号

1
2
3
4
5
SQL> 
select 
ts# 
from 
v$tablespace 
where 
name
=
'E2TEST'
;
 
       
TS#
----------
         
5

2、使用上述方法清理

1
2
3
SQL> 
alter 
session 
set 
events 
'immediate trace name DROP_SEGMENTS level 6'
;
 
Session altered.

3、再次查看最大的TEMPORY段已经没有了，表空间也彻底被释放了

1
2
3
4
5
6
7
8
9
10
11
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 < 50;
 
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%

     本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1877451，如需转载请自行联系原作者