alter table move 和 alter table shrink space的区别是什么

70次阅读

共计 6236 个字符，预计需要花费 16 分钟才能阅读完成。

alter table move 和 alter table shrink space 的区别是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

move 和 shrink 的共同点
1、收缩段
2、消除部分行迁移
3、消除空间碎片
4、使数据更紧密

shrink

语法：
alter table TABLE_NAME shrink space [compact|cascate]

segment shrink 执行的两个阶段：
1、数据重组(compact):

通过一系列 insert、delete 操作，将数据尽量排列在段的前面。在这个过程中需要在表上加 RX 锁，即只在需要移动的行上加锁。
由于涉及到 rowid 的改变，需要 enable row movement. 同时要 disable 基于 rowid 的 trigger. 这一过程对业务影响比较小。

2、HWM 调整：第二阶段是调整 HWM 位置，释放空闲数据块。

此过程需要在表上加 X 锁，会造成表上的所有 DML 语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。
注意：shrink space 语句两个阶段都执行。
shrink space compact 只执行第一个阶段。
如果系统业务比较繁忙，
可以先执行 shrink space compact 重组数据, 然后在业务不忙的时候再执行 shrink space 降低 HWM 释放空闲数据块。

举例

alter table TABLE_NAME shrink space compact; 只整理碎片不回收空间，
alter table TABLE_NAME shrink space; 整理碎片并回收空间。

alter table TABLE_NAME shrink space cascade; 整理碎片回收空间并连同表的级联对象一起整理(比如索引)
– 分区表
alter table ticket modify PARTITION P28071 shrink space cascade

shrink 的优点
1. 可在线执行
2. 可使用参数 cascade，同时收缩表上的索引
3. 执行后不会导致索引失效
4. 可避免 alter table move 执行过程中占用很多表空间（如果表 10G 大小，那 alter table move 差不多还得需要 10G 空间才能执行）。

shrink 使用条件:
使用步骤
1. alter table t1 enable ROW MOVEMENT;
2. shrink 操作
3. alter table t1 disable ROW MOVEMENT;

shrink 使用限制：
Shrink 操作需满足表空间是本地管理和自动段空间管理（10g、11g 默认就是这样），
以下情况不能用 shrink：

IOT 索引组织表
用 rowid 创建的物化视图的基表
带有函数索引的表
SECUREFILE 类型的大对象
压缩表

move

move 解决的问题
1、将一个 table 从当前的 tablespace 上移动到另一个 tablespace 上：

alter table t move tablespace tablespace_name;
alter table TABLE_NAME move ; – 在原来的表空间内部移动。

2、来改变 table 已有的 block 的存储参数, 如：

alter table t move storage (initial 30k next 50k);

3、另外，move 操作也可以用来解决 table 中的行迁移的问题。

使用 move 的一些注意事项：
1、table 上的 index 需要 rebuild：
在前面我们讨论过，move 操作后，数据的 rowid 发生了改变，我们知道，index 是通过 rowid 来 fetch 数据行的，所以，table 上的 index 是必须要 rebuild 的。
alter index index_name rebuild online；
2、move 时对 table 的锁定
当我们对 table 进行 move 操作时，查询 v$locked_objects 视图可以发现，table 上加了 exclusive lock
3、关于 move 时空间使用的问题：
当我们使用 alter table move 来降低 table 的 HWM 时，有一点是需要注意的，这时，当前的 tablespace 中需要有 1 倍于 table 的空闲空间以供使用。

move 和 hrink 的区别是：
1、move 后，表在表空间中的位置肯定会变，可能前移也可能后移，一般来说如果该表前面的表空间中有足够空间容纳该表，则前移，否则后移。
2、hrink 后，表在表空间中的位置肯定不变，也就是表的段头位置不会发生变化。

3、Move 会移动高水位，但不会释放申请的空间，是在高水位以下 (below HWM) 的操作。
4、shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下 (below and above HWM) 都有的操作。

5、使用 move 时，会改变一些记录的 ROWID，所以 MOVE 之后索引会变为无效，需要 REBUILD。
6、使用 shrink space 时，索引会自动维护。如果在业务繁忙时做压缩，
可以先 shrink space compact，来压缩数据而不移动 HWM，等到不繁忙的时候再 shrink space 来移动 HWM。

7、shrink 可以单独压缩索引，alter index xxx shrink space 来压缩索引。另外、压缩表时指定 Shrink space cascade 会同时压缩索引，

测试

SQL
SQL drop table test purge;
SQL drop table test2 purge;
SQL
SQL create table test (id number) storage (initial 10m next 1m) tablespace users;
SQL create table test2 (id number) storage (initial 10m next 1m) tablespace users;
SQL
SQL insert into test values(1);
SQL insert into test2 values(1);
SQL
SQL analyze table test compute statistics;
SQL analyze table test2 compute statistics;
SQL
SQL col SEGMENT_NAME for a10;
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST , TEST2
SEGMENT_NA EXTENTS BLOCKS INIT
———- ————————————– ————————————– ————————————–
TEST2 3 1280 10
TEST 3 1280 10
– 两个表，原始申请的分区数和数据块数
SQL col TABLE_NAME for a10;
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST , TEST2
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ————————————– ————————————–
TEST 46 1234
TEST2 46 1234
– 两个表，实际使用的数据块数 46，空闲数据块数 1234。
SQL
SQL begin
2 for i in 1..100000 loop
3 insert into test values(i);
4 insert into test2 values(i);
5 end loop;
6 end;
7 /
SQL
SQL analyze table test compute statistics;
SQL analyze table test2 compute statistics;
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST , TEST2
SEGMENT_NA EXTENTS BLOCKS INIT
———- ————————————– ————————————– ————————————–
TEST2 3 1280 10
TEST 3 1280 10
– 插入大量数据后，两个表的原始申请分区数和数据块数，没有变化
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST , TEST2
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ————————————– ————————————–
TEST 174 1106
TEST2 174 1106
– 插入大量数据后，两个表实际使用的数据块数发生了变化，使用 174 块，空闲 1106 块。174 就是高水位线
SQL
SQL
SQL delete from test where rownum =50000;
SQL delete from test2 where rownum =50000;
SQL
SQL analyze table test compute statistics;
SQL analyze table test2 compute statistics;
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST , TEST2
SEGMENT_NA EXTENTS BLOCKS INIT
———- ————————————– ————————————– ————————————–
TEST2 3 1280 10
TEST 3 1280 10
– 删除大量数据后，两个表的原始申请分区数和数据块数，没有变化

SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST , TEST2
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ————————————– ————————————–
TEST 174 1106
TEST2 174 1106
– 删除大量数据后，两个表实际使用的数据块数也没有发生变化。即 delete 不会释放空间

SQL
SQL
SQL alter table test move;
SQL
SQL analyze table test compute statistics;
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST , TEST2
SEGMENT_NA EXTENTS BLOCKS INIT
———- ————————————– ————————————– ————————————–
TEST2 3 1280 10
TEST 3 1280 10
– 对 test 表，做 move 操作，原始申请分区和数据块数，没有变化。

SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST , TEST2
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ————————————– ————————————–
TEST 95 1185
TEST2 174 1106
– 对 test 表，做 move 操作，实际使用数据块数发生变化。
Move 会移动高水位，但不会释放申请的空间，是在高水位以下 (below HWM) 的操作。

SQL
SQL
SQL alter table test2 enable row movement;
SQL alter table test2 shrink space;
SQL analyze table test2 compute statistics;
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST , TEST2
SEGMENT_NA EXTENTS BLOCKS INIT
———- ————————————– ————————————– ————————————–
TEST2 1 104 10
TEST 3 1280 10
– 对 test2 表，做 shrink 操作，原始申请分区和数据块数，发生了变化
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST , TEST2
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ————————————– ————————————–
TEST 95 1185
TEST2 79 25
– 对 test2 表，做 shrink 操作，实际使用数据块数，发生了变化
shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM) 都有的操作。