Oracle表的空间释放，碎片整理记录

一、表的占用空间大小释放原理

背景：

生产环境中，经常会遇到表由于数据不断插入，导致空间越来越大，由于前期配置问题，没有做分区或者其他优化，而且生产数据实时向表插入。要删除历史数据来释放空间。

分析：

（1）没做分区表：

由于没有分区表，所以无法分块对表进行清理。

（2）表实时插入数据，数据量大，要保留部分数据：

无法采用将有效数据插入另一个表，drop原表，修改名字来删除原表，或者truncate原表，来释放空间。

（3）删除历史数据：

用delete from 加条件，删除历史数据。

现象：

例子：wxk_big_tab_bak 表。

SELECT COUNT(*) FROM wxk_big_tab_bak

168522

 

SQL> selectround(BYTES/1024/1024,2)||'M' from user_segments where segment_name='WXK_BIG_TAB_BAK';

ROUND(BYTES/1024/1024,2)||'M'

-----------------------------------------

21M

 

对wxk_big_tab_bak 进行delete操作。

DECLARE

   row_num number:= 0;

begin

   for gyj_testIN (select ROWID from wxk_big_tab_bak twhere t.id  <=168522 ) LOOP

  delete from wxk_big_tab_bak  WHEREROWID = gyj_test.rowid ;

   row_num := row_num +1;

---100条提交一次,可根据需要修改

   if mod(row_num,100)= 0 THEN

     commit;

   end if;

end loop;

commit;

end;

/

执行删除后

SELECT COUNT(*) FROM wxk_big_tab_bak

0

再次查看

SQL> selectround(BYTES/1024/1024,2)||'M' from user_segments where segment_name='WXK_BIG_TAB_BAK';

ROUND(BYTES/1024/1024,2)||'M'

-----------------------------------------

21M

 

还是21M没有变化。

但是表里已经没有数据了。

这里注意，再次用插入命令新的数据，表的大小没有变化。由此可见，数据的空间确实被释放了，可以存储其他信息，但是从查询上看，表占用的空间大小没有缩小，没有整理表碎片。（高水位问题）。

 

如果需要将表的空间进行缩小，有两个办法：

（1）move

（2）shrink

1、对于空间的要求，shrink不需要额外的空间，move需要两倍的空间

2、shrink的算法是从segment的底部开始，移动row到segment的顶部，移动的过程相当于delete/insert操作的组合，在这个过程中会产生大量的undo和redo信息。

3、move是直接移动数据块的位置，鉴于上面的原因，在使用shrink的时候，耗时可能非常长，通常慢于move。

4、move是不能在线进行的，而且move后相应的索引也会失效。shrink的一个优点是能在线进行，不影响表上的DML操作，当然，并发的DML操作在shrink结束的时刻会出现短暂的block；shrink的另外一个优点是在碎片整理结束后，表上相关的index仍然enable。

 

对于一般系统，可采用shrink方式（仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)）：

（1）启用行记录转移(enable row movement)        

alter table wxk_big_tab_bak enablerow movement ;

（2）这里先执行compact 再 到空闲时间执行 cascade

ALTER TABLE wxk_big_tab_bak SHRINKSPACE COMPACT

ALTER TABLE wxk_big_tab_ba SHRINKSPACE cascade 

--compact:仅仅是缩小表和索引，并不移动高水位线，不释放空间

--cascade:缩小表及其索引，并移动高水位线，释放空间

 

对于重要系统，最好还是采用move

（1）move前最好逻辑备份待整理的表；

（2）--查看索引

select index_name,table_name,tablespace_name,index_type,status from dba_indexes  where table_owner='WXK'
（3）对于大表，建议开启并行和nologging
    alter table wxk_big_tab_bak MOVEnologging parallel 2;
（4）整理完毕后重建相关的索引
    alterindex SYS_C0011093rebuild nologgingparallel 2;
（5）恢复表和索引的并行度、logging
  alter table wxk_big_tab_bak loggingparallel 1;

 

最后再次查看

SQL>select round(BYTES/1024/1024,2)||'M' from user_segments wheresegment_name='WXK_BIG_TAB_BAK';

 

ROUND(BYTES/1024/1024,2)||'M'

-----------------------------------------

.06M

此时，表占用空间被释放。

本文再附一个表空间利用率sql

SELECT A.TABLESPACE_NAME,      
       FILENUM,   
       TOTAL "TOTAL (MB)",  
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%", 
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",    
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,          
               COUNT(FILE_ID) FILENUM,        
               SUM(BYTES / (1024 * 1024)) TOTAL,          
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES      
          FROM DBA_DATA_FILES       
         GROUP BY TABLESPACE_NAME) A,     
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE     
          FROM DBA_FREE_SPACE      
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME