oracle shrink 表收缩功能

表收缩

作用：

降低表的高水位线，回收表的实际存储空间
注意：shrink操作会产生redo 日志

实验：

1.创建测试数据：

CREATE TABLE test_s AS
SELECT MOD(LEVEL,5) col1,dbms_random.string('A',20) col2
FROM dual
CONNECT BY LEVEL<=100000;

2.分析表

ANALYZE TABLE test_s COMPUTE STATISTICS;

3.查看表统计信息及数据块空闲情况

SQL> SELECT T.BLOCKS,T.NUM_ROWS*T.AVG_ROW_LEN/1024/1024 "m" FROM dba_tables t WHERE t.TABLE_NAME='TEST_S';

    BLOCKS          m
---------- ----------
       415 2.57492065

SQL> exec show_space_assm('TEST_S','MAN_DEV');

free space 0-25% Blocks:................0
free space 25-50% Blocks:...............0
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................399
Unformatted blocks:.....................0

PL/SQL procedure successfully completed

4.删除部分数据后查看各指标

SQL> delete from test_s where col1=0;

20000 rows deleted


SQL> commit;

Commit complete

SQL> analyze table test_s compute statistics;

Table analyzed


SQL> exec show_space_assm('TEST_S','MAN_DEV');

free space 0-25% Blocks:................0
free space 25-50% Blocks:...............398
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................1
Unformatted blocks:.....................0

PL/SQL procedure successfully completed


SQL> SELECT T.BLOCKS,T.NUM_ROWS*T.AVG_ROW_LEN/1024/1024 "m" FROM dba_tables t WHERE t.TABLE_NAME='TEST_S';

    BLOCKS          m
---------- ----------
       415     2.05993652

SQL> SELECT * FROM v$statname t,v$mystat b WHERE t.STATISTIC#=b.STATISTIC# AND t.NAME='redo size';

STATISTIC# NAME                                                                  CLASS    STAT_ID        SID STATISTIC#      VALUE
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
       194 redo size                                                                 2 1236385760         67        194    5828844

5.收缩表：
语法：
ALTER TABLE test_s ENABLE ROW MOVEMENT;
ALTER TABLE test_s SHRINK SPACE CASCADE;

6.再次查看各指标

SQL> SELECT * FROM v$statname t,v$mystat b WHERE t.STATISTIC#=b.STATISTIC# AND t.NAME='redo size';

STATISTIC# NAME                                                                  CLASS    STAT_ID        SID STATISTIC#      VALUE
---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- ----------
       194 redo size                                                                 2 1236385760         67        194   14378672
       
**redo 日志增大**
SQL> analyze table test_s compute statistics;

Table analyzed


SQL> SELECT T.BLOCKS,T.NUM_ROWS*T.AVG_ROW_LEN/1024/1024 "m" FROM dba_tables t WHERE t.TABLE_NAME='TEST_S';

    BLOCKS          m
---------- ----------
       322 2.05993652
**blocks 减少**  

SQL> exec show_space_assm('TEST_S','MAN_DEV');

free space 0-25% Blocks:................0
free space 25-50% Blocks:...............3
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................319
Unformatted blocks:.....................0

PL/SQL procedure successfully completed
空闲块变少，整体blocks变少

附上过程show_space_assm详细代码：

CREATE OR REPLACE PROCEDURE show_space_assm(p_segname IN VARCHAR2
                                           ,p_owner   IN VARCHAR2 DEFAULT USER
                                           ,p_type    IN VARCHAR2 DEFAULT 'TABLE') AS
   l_fs1_bytes          NUMBER;
   l_fs2_bytes          NUMBER;
   l_fs3_bytes          NUMBER;
   l_fs4_bytes          NUMBER;
   l_fs1_blocks         NUMBER;
   l_fs2_blocks         NUMBER;
   l_fs3_blocks         NUMBER;
   l_fs4_blocks         NUMBER;
   l_full_bytes         NUMBER;
   l_full_blocks        NUMBER;
   l_unformatted_bytes  NUMBER;
   l_unformatted_blocks NUMBER;
   PROCEDURE p(p_label IN VARCHAR2
              ,p_num   IN NUMBER) IS
   BEGIN
      dbms_output.put_line(rpad(p_label, 40, '.') || p_num);
   END;
BEGIN
   dbms_space.space_usage(segment_owner      => p_owner,
                          segment_name       => p_segname,
                          segment_type       => p_type,
                          fs1_bytes          => l_fs1_bytes,
                          fs1_blocks         => l_fs1_blocks,
                          fs2_bytes          => l_fs2_bytes,
                          fs2_blocks         => l_fs2_blocks,
                          fs3_bytes          => l_fs3_bytes,
                          fs3_blocks         => l_fs3_blocks,
                          fs4_bytes          => l_fs4_bytes,
                          fs4_blocks         => l_fs4_blocks,
                          full_bytes         => l_full_bytes,
                          full_blocks        => l_full_blocks,
                          unformatted_blocks => l_unformatted_blocks,
                          unformatted_bytes  => l_unformatted_bytes);
   p('free space 0-25% Blocks:', l_fs1_blocks);
   p('free space 25-50% Blocks:', l_fs2_blocks);
   p('free space 50-75% Blocks:', l_fs3_blocks);
   p('free space 75-100% Blocks:', l_fs4_blocks);
   p('Full Blocks:', l_full_blocks);
   p('Unformatted blocks:', l_unformatted_blocks);
END;

关于分析表详细介绍：
(https://blog.csdn.net/qq_33371150/article/details/102556087)