shrink

 shrink操作：

alter table TABLE_NAME shrink space [compact|cascate]

  alter table TABLE_NAME shrink space; 整理碎片并回收空间
  alter table TABLE_NAME shrink space compact; 只整理碎片 不回收空间
  alter table TABLE_NAME shrink space cascate; 整理碎片回收空间 并连同表的级联对象一起整理(比如索引)

  alter index index_name shrink space; 整理索引碎片，回缩索引碎片空间高水位

 

操作：

1. alter table t1 enable  ROW MOVEMENT;
  2. shrink 操作
  3. alter table t1 disable  ROW MOVEMENT;

对比：

1 move时产生的日志比shrink时少

 

2 shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息，当然shrink过程中用来维护index的成本也会比较高。而表move后index的状态是UNUSABLE的,需要进行rebuild

3oracle是从后向前移动行数据，那么，shrink的操作就不会像move一样，shrink不需要使用额外的空闲空间

 

实验一：alter table TABLE_NAME shrink space 会回收高水位

SQL> create table t1 as select *From emp;

表已创建。

SQL> insert into t1 select *From t1;

已创建12行。

 
SQL> select count(*)from t1;

  COUNT(*)
----------
    393216

SQL> delete t1;

已删除393216行。

SQL> commit;

提交完成。

 

http://blog.csdn.net/wll_1017/article/details/8662730    

查看 show_space()命令

 

查看表里的块：

SQL> exec dbms_stats.gather_table_stats(ownname =>'SCOTT',tabname=>'T1',estimate_percent => 100 ,method_opt =>'for all columns size 1',degree=>2,cascade => true);

PL/SQL 过程已成功完成。

 

SQL> exec show_space('T1','T','AUTO','Y',NULL)
Total Blocks............................2432
Total Bytes.............................19922944
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................2432
Last Used Block.........................128
Thesegment is analyzed
0% -- 25% free spaceblocks..............0
0% -- 25% free spacebytes...............0
25% -- 50% free spaceblocks.............0
25% -- 50% free spacebytes..............0
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............2324
75% -- 100% free spacebytes.............19038208
Unused Blocks...........................62
Unused Bytes............................507904
Total Blocks............................0
Total bytes.............................0

PL/SQL 过程已成功完成。

 

 

SQL>  alter table T1 enable row movement;

表已更改。

SQL>  alter table T1 shrink space compact;

表已更改。

SQL> exec show_space('T1','T','AUTO','Y',NULL);
Total Blocks............................2432
Total Bytes.............................19922944
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................2432
Last Used Block.........................128
Thesegment is analyzed
0% -- 25% free spaceblocks..............0
0% -- 25% free spacebytes...............0
25% -- 50% free spaceblocks.............0
25% -- 50% free spacebytes..............0
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............2324
75% -- 100% free spacebytes.............19038208
Unused Blocks...........................62
Unused Bytes............................507904
Total Blocks............................0
Total bytes.............................0

PL/SQL 过程已成功完成。

SQL> alter table T1 shrink space ;

表已更改。

SQL> exec show_space('T1','T','AUTO','Y',NULL);
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................4
Last Used Ext BlockId...................312
Last Used Block.........................4
Thesegment is analyzed
0% -- 25% free spaceblocks..............0
0% -- 25% free spacebytes...............0
25% -- 50% free spaceblocks.............0
25% -- 50% free spacebytes..............0
50% -- 75% free spaceblocks.............0
50% -- 75% free spacebytes..............0
75% -- 100% free spaceblocks............1
75% -- 100% free spacebytes.............8192
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL 过程已成功完成。

SQL> alter table T1 disable row movement;

表已更改。

SQL> select distinct sid from v$mystat;

       SID
----------
        72

在测试shrink是另外开一个窗口测试锁的状况：

SQL> select *From v$lock where sid=72;

ADDR      KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
--------                      -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
3D378960   3D37898C         72 AE        100          0          4          0      13334          0                  
3D378A48   3D378A74         72 OD      88182          0          3          0         17          0 
3D378B30  3D378B5C         72 TO      65921          1          3          0      13323          0
3D378F44   3D378F70         72 SK          4   16777530          6          0         17          0
435EC2B0  435EC2E0         72 TM      88182          0          3          0         17          0
3B0AA158    3B0AA198         72 TX     393244       8920          6          0         17          0
3B0AA79C   3B0AA7DC         72 TX     393236       8976          6          0          0          0

结论：alter table T1 shrink space ;  可以回收高水位线

 

 

 实验二：shrink space 不会改变rowid，但是move会改变rowid

SQL> select empno,
  2            ename,
  3            rowid,
  4            dbms_rowid.rowid_object(rowid) object_id,
  5            dbms_rowid.rowid_relative_fno(rowid) file_id,
  6             dbms_rowid.rowid_block_number(rowid) block_id,
  7             dbms_rowid.rowid_row_number(rowid) num
  8        from t1;

  EMPNO ENAME      ROWID  OBJECT_ID    FILE_IDBLOCK_IDNUM
---------- ---------- ------------------ ---------- ---------- ---------- ----------
      7369 SMITH      AAAMitAAEAAAAA8AAA      51373    4      60  0
      7499 ALLEN      AAAMitAAEAAAAA8AAB      51373    4      60  1
      7521 WARD       AAAMitAAEAAAAA8AAC      51373    4      60  2
      7566 JONES      AAAMitAAEAAAAA8AAD      51373    4      60  3
      7654 MARTIN     AAAMitAAEAAAAA8AAE      51373    4      60  4
      7698 BLAKE      AAAMitAAEAAAAA8AAF      51373    4      60  5
      7782 CLARK      AAAMitAAEAAAAA8AAG      51373    4      60  6
      7788 SCOTT      AAAMitAAEAAAAA8AAH      51373    4      60  7
      7839 KING       AAAMitAAEAAAAA8AAI      51373    4      60  8
      7844 TURNER     AAAMitAAEAAAAA8AAJ      51373    4      60  9
      7876 ADAMS      AAAMitAAEAAAAA8AAK      51373    4      60 10
      7900 JAMES      AAAMitAAEAAAAA8AAL      51373    4      60 11
      7902 FORD       AAAMitAAEAAAAA8AAM      51373    4      60 12
      7934 MILLER     AAAMitAAEAAAAA8AAN      51373    4      60 13
   

SQL> delete t1 where deptno=10;
3 rows deleted.

SQL> delete t1 where deptno=30;
6 rows deleted.

SQL> commit;
Commit complete.

SQL>  select empno,
            ename,
              rowid,
              dbms_rowid.rowid_object(rowid) object_id,
              dbms_rowid.rowid_relative_fno(rowid) file_id,
               dbms_rowid.rowid_block_number(rowid) block_id,
               dbms_rowid.rowid_row_number(rowid) num
          from t1;
     EMPNO ENAME      ROWID  OBJECT_ID    FILE_IDBLOCK_IDNUM
---------- ---------- ------------------ ---------- ---------- ---------- ----------
      7369 SMITH      AAAMitAAEAAAAA8AAA      51373    4      60  0
      7566 JONES      AAAMitAAEAAAAA8AAD      51373    4      60  3
      7788 SCOTT      AAAMitAAEAAAAA8AAH      51373    4      60  7
      7876 ADAMS      AAAMitAAEAAAAA8AAK      51373    4      60 10
      7902 FORD       AAAMitAAEAAAAA8AAM      51373    4      60 12

SQL> alter table t1 enable row movement;
Table altered.

SQL> alter table t1 shrink space;
Table altered.

SQL> alter table t1 disable  row movement;
Table altered.

SQL>  select empno,

              ename,

              rowid,

              dbms_rowid.rowid_object(rowid) object_id,

              dbms_rowid.rowid_relative_fno(rowid) file_id,

               dbms_rowid.rowid_block_number(rowid) block_id,

               dbms_rowid.rowid_row_number(rowid) num

          from t1;

     EMPNO ENAME      ROWID  OBJECT_ID    FILE_IDBLOCK_IDNUM

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

      7369 SMITH      AAAMitAAEAAAAA8AAA      51373    4      60  0

      7566 JONES      AAAMitAAEAAAAA8AAD      51373    4      60  3

      7788 SCOTT      AAAMitAAEAAAAA8AAH      51373    4      60  7

      7876 ADAMS      AAAMitAAEAAAAA8AAK      51373    4      60 10

      7902 FORD       AAAMitAAEAAAAA8AAM      51373    4      60 12

 结论：shrink不会改变rowid

SQL> alter table t1 move;
Table altered.

SQL>  select empno,

                 ename,

              rowid,

              dbms_rowid.rowid_object(rowid) object_id,

              dbms_rowid.rowid_relative_fno(rowid) file_id,

               dbms_rowid.rowid_block_number(rowid) block_id,

               dbms_rowid.rowid_row_number(rowid) num

          from t1;

     EMPNO ENAME      ROWID  OBJECT_ID    FILE_IDBLOCK_IDNUM

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

      7369 SMITH      AAAMiuAAEAAAABEAAA      51374    4      68  0

      7566 JONES      AAAMiuAAEAAAABEAAB      51374    4      68  1

      7788 SCOTT      AAAMiuAAEAAAABEAAC      51374    4      68  2

      7876 ADAMS      AAAMiuAAEAAAABEAAD      51374    4      68  3

      7902 FORD       AAAMiuAAEAAAABEAAE      51374    4      68  4

结论：move 后数据重新码放，按照ABCD的顺序排列

 

 

使用alter table tname shrink 和alter table tname  move tablespace spacename  在表上都会加3号锁和6号锁