oracle 收缩表、清理碎片，释放空间

可以用来收缩段，消除空间碎片的方法有两种：

1.alter table table_name move

需要注意：

1）move操作会锁表。（如果是很小的表，可以在线做。如果是大表一定要注意，会长时间锁表，只能查询，影响正常业务运行。）
2）move操作会使索引失效，一定要rebuild。（因为move操作会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。）

2.使用shrink space

alter table table_name shrink space

前提条件

1) 必须启用行记录转移(enable row movement)

2) 仅仅适用于堆表,且位于自动段空间管理的表空间(堆表包括:标准表,分区表,物化视图容器,物化视图日志表)

优点：

提高缓存利用率，提高OLTP的性能

减少磁盘I/O，提高访问速度，节省磁盘空间

段收缩是在线的，索引在段收缩期间维护，不要求额外的磁盘空间

加参数

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

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

如果在业务繁忙时做压缩，

可以使用alter table shrink space compact来对表格进行碎片整理，而不调整高水位线，之后再次调用alter table table_name shrink space来释放空间。

也可以使用alter table table_name shrink space cascade来同时对索引都进行收缩，这等同于同时执行alter index idxname shrink space。

方法一：move方式收缩表

1）创建一张新表test，并插入数据

SQL> insert into TEST values (3, 'cc' ); 




--查看test表中rowid

SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST;

2) 删除表中部分数据，并再次查看表中rowid

SQL> delete from TEST where mod(id,2)=1; 

SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST;

3) 对表执行move操作

SQL> alter table TEST move ; 

4)再次查看表中rowid  

SQL> select Dbms_Rowid.rowid_block_number(rowid)  from TEST;

小结： 1

move操作后，数据的rowid发生了改变，我们知道，index是通过rowid来获取数据行的，所以table上的index是必须要rebuild的。

5） 查看表中索引情况，此时索引为失效的

SQL> select index_name,status from user_indexes where index_name='TEST_INDEX';

INDEX_NAME                        STATUS

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

TEST_INDEX                          UNUSABLE

6）在线重建索引

SQL> alter index TEST_MOVE_INDEX rebuild online;




小结：2

move操作后，表中索引会失效

--查看锁情况

SQL> SELECT b.session_id AS sid,  

          NVL(b.oracle_username, '(oracle)') AS username,  

          a.owner AS object_owner,  

          a.object_name,  

           Decode(b.locked_mode, 0, 'None',  

                                1, 'Null (NULL)',  

                               2, 'Row-S (SS)',  

                                3, 'Row-X (SX)',  

                                4, 'Share (S)',  

                                5, 'S/Row-X (SSX)',  

                               6, 'Exclusive (X)',  

                               b.locked_mode) locked_mode,  

           b.os_user_name  

    FROM   dba_objects a,  

         v$locked_object b  

    WHERE  a.object_id = b.object_id;

      SID          USERNAME          OBJECT_OWNER       OBJECT_NAME        LOCKED_MODE        OS_USER_NAME

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

        33          YMM                          YMM                    TEST                         Exclusive (X)                  oracle

小结：3

--Exclusive (X) 是6号锁，独占锁。  

--这就意味着，table在进行move操作时，我们只能对它进行select的操作。

也就是说当我们的一个session对table进行DML操作且没有commit时，在另一个session中是不能对这个table进行move操作的，

否则oracle会返回这样的错误信息：ORA-00054 。

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NAME          EXTENTS      BLOCKS      INIT

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

TEST                                   3               1280         10

--TEST表初始分配了10M的空间，1280个BLOCKS。

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME               BLOCKS   EMPTY_BLOCKS

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

TEST

--USER_TABLES视图显示有0个使用的BLOCKS，1280个空闲BLOCKS。

--向表中插入数据

SQL> insert into TEST select * from information;

SQL> analyze table TEST compute statistics;

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NAME          EXTENTS  BLOCKS       INIT

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

TEST                                 3              1280         10

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

TEST                                        1006          274

--插入数据后，分配的空间仍不变，因为10M还没使用完。显示使用了1006个BLOCKS，空闲274个BLOCKS。这时候的1006 BLOCKS即是高水位线。

SQL> commit;

SQL> select count(*) from test;

  COUNT(*)

----------

    122513

SQL> delete from test  where rownum<=50000;

SQL> analyze table test compute statistics;

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NAME                EXTENTS     BLOCKS       INIT

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

TEST                                       3               1280         10

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

TEST                                         1006          274

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;

USED_BLOCKS

-----------

        573

--这边可以看到，删掉部分数据后，仍然显示使用了1006个BLOCKS，高水位没变。但查询真正使用的BLOCK数只有573个。所以DELETE操作是不会改变HWM的。

SQL> alter table TEST move;   

SQL> analyze table TEST compute statistics;

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NAME                 EXTENTS     BLOCKS       INIT

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

TEST                                         3              1280         10

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME           BLOCKS EMPTY_BLOCKS

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

TEST                           592          688

小结：4

--MOVE之后,HWM降低了，空闲块也上去了。

--但是分配的空间并没有改变，仍然是1280个BLOCKS。

方法二：shrink space方式收缩表

SQL> delete from test  where rownum<=50000;

--首先设置允许行迁移

SQL> alter table TEST enable row movement;

SQL> alter table TEST  shrink space;

SQL> analyze table TEST compute statistics;     -->使用analyze更新统计信息后EMPTY_BLOCKS得到数据  

SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';

SEGMENT_NAME             EXTENTS         BLOCKS       INIT

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

TEST                                     1                   600            10

SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';

TABLE_NAME                         BLOCKS    EMPTY_BLOCKS

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

TEST                                         592            8

--SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是回收高水位线操作。

验证cascade与compact的差异   

--删除一些数据

SQL> delete from test where rownum<8000;                                                     

                                                                                                                   

SQL> alter table test shrink space compact;  -->使用compact方式收缩表段                                                                                                                                                                                                                    

SQL> exec show_space('TEST','SCOTT');                                                                           

Unformatted Blocks .....................               0                                                             

FS1 Blocks (0-25) ......................               1                                                             

FS2 Blocks (25-50) .....................               2                                                             

FS3 Blocks (50-75) .....................               0                                                             

FS4 Blocks (75-100).....................             103                                                             

Full Blocks ............................          14,214 --仅有的变化为14318-14214=104块，即完全填满的数据块减少了104块

Total Blocks............................          14,488 --数据的总块数及总大小并没有减少，即未移动高水位线          

Total Bytes.............................     118,685,696                                                             

Total MBytes............................             113                                                             

Unused Blocks...........................               5                                                             

Unused Bytes............................          40,960                                                             

Last Used Ext FileId....................               4                                                             

Last Used Ext BlockId...................          16,521                                                             

Last Used Block.........................             147                                                             

                                                                                                                     

PL/SQL procedure successfully completed.                                                                             

                                                                                                                     

SQL> alter table test shrink space cascade;  -->使用cascade方式收缩                                                                                                                                              

                                                                                                                     

SQL> exec show_space('TEST','SCOTT');                                                                           

Unformatted Blocks .....................               0                                                             

FS1 Blocks (0-25) ......................               1                                                             

FS2 Blocks (25-50) .....................               2                                                             

FS3 Blocks (50-75) .....................               0                                                             

FS4 Blocks (75-100).....................               0                                                             

Full Blocks ............................          14,214                                                             

Total Blocks............................          14,384   -->总块数及总大小均已减少                                 

Total Bytes.............................     117,833,728                                                             

Total MBytes............................             112                                                             

Unused Blocks...........................               4                                                             

Unused Bytes............................          32,768                                                             

Last Used Ext FileId....................               4                                                             

Last Used Ext BlockId...................          16,521                                                             

Last Used Block.........................              44                                                             

                                                                                                                     

PL/SQL procedure successfully completed.                                                                             

                                                                                                                     

-->收缩之后索引依然有效                                                                                              

SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='TEST';                                   

                                                                                                                     

OWNER              INDEX_NAME         STATUS                                                 

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

SCOTT                    idx_test                 VALID     

小结：

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

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

语法总结：

ALTER TABLE <table_name> ENABLE ROW MOVEMENT   -->前提条件                                       

                                                                                                 

ALTER TABLE <table_name> SHRINK SPACE [ <NULL> | COMPACT | CASCADE ];                            

                                                                                                 

ALTER TABLE <table_name> SHRINK SPACE COMPCAT;  -->缩小表和索引，不移动高水位线，不释放空间      

                                                                                                 

ALTER TABLE <table_name> SHRINK SPACE;     -->收缩表，降低高水位线;                              

                                                                                                 

ALTER TABLE <table_name> SHRINK SPACE CASCADE; -->收缩表，降低高水位线，并且相关索引也要收缩一下 

                                                                                                 

ALTER TABLE <table_name> MODIFY LOB (lob_column) (SHRINK SPACE);  -->收缩LOB段                   

                                                                                                 

ALTER INDEX IDXNAME SHRINK SPACE;