共计 7836 个字符，预计需要花费 20 分钟才能阅读完成。
这篇文章将为大家详细讲解有关 alter table move 与 shrink space 的区别是什么，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。
一、move
案例：
同事将一关键表中删了多余的 300w 条数据后，程序就变的异常缓慢。分析得出，应该是表空间碎片过多，旧的索引效率过低。
执行下面两句话：
alter table ycsbt_qyygxx_jb move;
alter index R_SBXX_YCSBD_FK rebuild online;
效果非常明显。
deltete 不会释放表空间，但是可以重用，也就是插入可以填补空洞，当然现实应用中确实是存在经常删除很少插入的情况，这样就存在了释放表空间优化数据库的可行性了，truncate 有不能带条件的缺陷，自然就想到用 alter table move 重移表空间的方法。这里要注意三个要素
1、 alter table move 省略了 tablespace XXX, 表示用户移到自己默认的表空间，因此当前表空间至少要是该表两倍大，这很好理解，由于易错所以提出，就不再细说了。
2、 alter table move 过程中会导致索引失效，必须要考虑重新索引
3、 alter table move 过程中会产生锁，应该避免在业务高峰期操作！
就第二点和第三点做实验说明如下
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
– 先获取该 SESSION 的 SID, 方便实验观察
SQL select sid from v$mystat where rownum=1;
SID
——————–
160
SQL create table ljb_test as select * from dba_objects;
Table created
SQL select count(*) from ljb_test;
COUNT(*)
——————-
62659
SQL create index idx_test on ljb_test(object_id);
Index created
– 查询当前该 SESSION 并无锁
SQL select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———-
– 查看索引状态也正常！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
—————————— —————————— ———————————————–
IDX_TEST LJB_TEST VALID
– 执行命令 alter table ljb_test move;
— 查询：
select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– —— —- ——- ———- —— ——–
—— —————-
2043451C 20434530 160 CF 0 0 4 0 0 0
1FA072BC 1FA073D8 160 TX 917534 592 6 0 1 0
204344C0 204344D4 160 HW 76 323783147 6 0 0 0
1F9C4224 1F9C423C 160 TM 84825 0 6 0 0 0
204342F4 20434308 160 TT 76 16 4 0 0 0
1F9C377C 1F9C37C4 160 TS 76 323783147 6 0 0 0
– 不过由于 alter table move 命令未结束，索引仍然有效！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
—————————— —————————— —————————————————-
IDX_TEST LJB_TEST VALID
– 等 alter table ljb_test move; 命令结束后，再查看发现锁消失了
SQL select * from v$lock where sid=160;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- —- ———- ———- ———-
———- ———-
– 但是索引却失效了！
SQL select index_name,table_name,status from user_indexes where table_name= LJB_TEST
INDEX_NAME TABLE_NAME STATUS
———————————————————————————-
IDX_TEST LJB_TEST UNUSABLE
总结：这个实验说明：除了知道 alter
table
move 命令可以释放空间（当然这语句最根本的作用还是移动表到不同的表空间去，这里只是借用它可以释放空间的一个特性），还要了解该动作会锁表直到命令结束，而且会导致索引失效，属于危险命令，建议千万不要在业务高峰期操作。
二、shrink
都知道 alter table move 或 shrink space 可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但 move 跟 shrink space 还是有区别的。
Move 会移动高水位，但不会释放申请的空间，是在高水位以下 (below HWM) 的操作。
而 shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下 (below and above HWM) 都有的操作。
使用 Shrink 有两个前提条件：
表必须启用 row movement
表段所在表空间的段空间管理 (segment space management) 必须为 auto
在线段收缩的语法如下：
alter table/index/materialized view object_name shrink space [cascade] [compact];
cascade：是指压缩所有依赖的对象，比如压缩表语句加上 cascade，表上所有的索引都会被压缩
compact：把压缩过程分为两个阶段：第一个阶段的语句带 compact，压缩段空间，在这个过程中需要在表上加 RX 锁，即只在需要移动的行上加锁。由于涉及到 rowid 的改变，需要 enable row movement. 同时要 disable 基于 rowid 的 trigger. 这一过程对业务影响比较小。；第二个阶段语句不带 compact，调整高水位并释放收回的空间。此过程需要在表上加 X 锁，会造成表上的所有 DML 语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。对于大表，建议采用 compact 选项。
也许很难理解吧，看测试就知道了。
SQL select * from v$version;
BANNER
—————————————————————-
oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL analyze table test compute statistics;
Table analyzed.
SQL col SEGMENT_NAME for a10
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 10 1280 10
SQL col TABLE_NAME for a10
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 1280
–TEST 表初始分配了 10M 的空间，可以看到有 10 个 EXTENTS，1280 个 BLOCKS。USER_TABLES 视图显示有 0 个使用的 BLOCKS，1280 个空闲 BLOCKS，即该 10M 空间内的 BLOCK 都还没被 ORACLE”格式化”。
SQL begin
for i in 1..100000 loop
insert into test values(i);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 186 1094
– 插入 10W 条数据后，分配的空间仍不变，因为 10 个 EXTENTS 还没使用完。显示使用了 186 个 BLOCKS，空闲 1094 个 BLOCKS。这时候的 186BLOCKS 即是高水位线
SQL delete from test where rownum =50000;
50000 rows deleted.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 186 1094
SQL select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
———–
77
– 这边可以看到，删掉一半数据后，仍然显示使用了 186 个 BLOCKS，高水位没变。但查询真正使用的 BLOCK 数只有 77 个。所以 DELETE 操作是不会改变 HWM 的
SQL alter table test move;
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 81 1199
–MOVE 之后,HWM 降低了，空闲块也上去了
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 10 1280
– 但是分配的空间并没有改变，仍然是 1280 个 BLOCKS。下面看用 SHRINK SPACE 的方式
SQL alter table test enable row movement;
Table altered.
SQL alter table test shrink space;
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS
———- ———- ———-
TEST 1 88
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 81 7
– 分配的空间已经降到最小，1 个 EXTENTS，88 个 BLOCKS
所以 MOVE 并不算真正意义上的压缩空间，只会压缩 HWM 以下的空间，消除碎片。我们一般建表时没有指定 initial 参数(默认是 8 个 BLOCK)，也就感觉不到这个差异。而 SHRINK
SPACE 真正做到了对段的压缩，包括初始分配的也压了，所以它是 blow and above HWM 操作。
至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的 HWM 高度，那显然 MOVE 是更合适的，因为 SHRINK SPACE 还需要重新申请之前放掉的空间，无疑增加了操作。
注意：
1. 不过用 MOVE 的方式也可以做到真正的压缩分配空间，只要指定 STORAGE 参数即可。
SQL drop table test;
Table dropped.
SQL create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 10 1280 10
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 1280
SQL alter table test move storage (initial 1m);
Table altered.
SQL analyze table test compute statistics;
Table analyzed.
SQL select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME= TEST
SEGMENT_NA EXTENTS BLOCKS INIT
———- ———- ———- ———-
TEST 16 128 1
SQL select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name= TEST
TABLE_NAME BLOCKS EMPTY_BLOCKS
———- ———- ————
TEST 0 128
2. 使用 move 时，会改变一些记录的 ROWID，所以 MOVE 之后索引会变为无效，需要 REBUILD。同时 move 表需要 2 倍的表空间，而 shrink 不需要
3. 使用 shrink space 时，索引会自动维护。如果在业务繁忙时做压缩，可以先 shrink space compact，来压缩数据而不移动 HWM，等到不繁忙的时候再 shrink space 来移动 HWM。
4. 索引也是可以压缩的，压缩表时指定 Shrink space cascade 会同时压缩索引，也可以 alter index xxx shrink space 来压缩索引。
5.shrink space 需要在表空间是自动段空间管理的，所以 system 表空间上的表无法 shrink space。
6.shrink 的限制
关于 alter table move 与 shrink space 的区别是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
