释放表中delete后的空间

一、现象：

从DBA_SEGMENTS里面看，数据库中最大的对象是两个LOB，通过和DBA_LOG关联，找到对应的2个表。以下为这4个对象的大小  。用delete语句删除了这两个表中的部分数据之后，这4个对象占用的空间不变。

Col l.segment_name for a100

Col l.table_name for a100

select l.segment_name,l.table_name,sum(s.bytes)/1024/1024/1024
from dba_lobs l,dba_segments s
where l.segment_name in
(
'SYS_LOB0000073056C00021$$','SYS_LOB0000072642C00004$$'
)
and l.segment_name=s.segment_name
group by l.segment_name,l.table_name

select segment_name,sum(bytes)/1024/1024/1024
from dba_segments
where segment_name in
(‘TBPATTACH’,’ TMONITORERROR’
)
group by segment_name
;

二、原因解释

在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-warter mark, HWM）。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

三、关于rowid

    等等，这事跟rowid有啥关系？O(∩_∩)O，有点关系，rowid的特性可以用来当作工具，查看表中有多少个块是空闲的。

    rowid是伪列，它的前15位可以确定对应的记录在哪个快上，通过这个特性，可以查看某个对象所用的块中，哪些有数据。

3、查看块的空闲率

执行以下sql，发现95%的块上是空的

select round((1 - a.used / b.num_total) * 100, 0) percent

  from (SELECT COUNT(DISTINCT SUBSTR(rowid, 1, 15)) Used

          FROM SEEASDB0.TBPINSTANCES) a,

       (select blocks num_total

          from dba_tables

         where table_name = 'xxxxxxx') b;

截图如下：

4、收缩表

--先收缩一个表和它的lob对象

alter table TMONITORERROR enable row movement;

alter table TMONITORERROR shrink space;

--执行以下sql无效

Alter table TMONITORERROR modify lob (CERRORSTACK) （shrink space cascade）；

--lob对象需要move

ALTER TABLE TMONITORERROR MOVE LOB(CERRORSTACK) STORE AS (TABLESPACE SEEDTA);

alter table TMONITORERROR disable row movement;

--再收缩另一个表和对象

alter table TBPATTACH enable row movement;

alter table TBPATTACH shrink space;

--执行以下sql无效

Alter table TBPATTACH modify lob (CATTACHMENT) （shrink space cascade）；

--lob对象需要move

ALTER TABLE TBPATTACH MOVE LOB(CATTACHMENT) STORE AS (TABLESPACE SEEDTA);

alter table TBPATTACH disable row movement;

5、查看表的索引是否可用

select owner,index_name ,status from dba_indexes where table_name='TBPATTACH';

 select index_name ,status from dba_indexes where table_name='TMONITORERROR';

alter index SEEASDB0.PK_TBPATTACH rebuild  online;

alter index SEEASDB0.IX_TBPATTACH_CCREATEDTIME rebuild online;

alter index SEEASDB0.PK__TMONITORERROR rebuild online;

alter index SEEASDB0. IDX_TMONITORCONTEXTID rebuild online;

alter index SEEASDB0. IDX_TMONITORERROR1 rebuild online;

alter index SEEASDB0. IDX_TMONITORERROR_CENV rebuild online;

alter index SEEASDB0. IDX_TMONITORERROR_CERRORTYPE rebuild online;

alter index SEEASDB0. IDX_TMONITORERROR_CNODECAPTION rebuild online;

alter index SEEASDB0. IDX_TMONITORERROR_EDATE_ISWARN rebuild online;

alter index SEEASDB0. IDX_TMONITORERROROID rebuild online;

alter index SEEASDB0. IDX_TMONITORLCHANGEDDATE rebuild online;