move 和 shrink都会移动表的高水位,但不同的是move不会释放已经申请的空间,move的时候会锁表,而且move会后索引会失效,shrink后会释放申请的空间,而且不会造成索引失效。此外碎片的整理也不一致。
使用shrink的时候要注意,表要开启行迁移enable row movement,且表段所在表空间的段空间管理(segment space management)必须为auto,所以system表空间上面的无法shrink,
1 2 3 4 5 6 7 |
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0 |
一. 首先我们来看一下碎片的整理情况
1. 建立测试表
123456789101112131415161718192021
SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000;Table created.SQL> select count(*) from test; COUNT(*)----------5000SQL> exec show_space('TEST');Unformatted Blocks ..................... 0FS1 Blocks (0-25) ...................... 0FS2 Blocks (25-50) ..................... 0FS3 Blocks (50-75) ..................... 0FS4 Blocks (75-100)..................... 0Full Blocks ............................ 26Total Blocks............................ 32Total Bytes............................. 262,144Total MBytes............................ 0Unused Blocks........................... 2Unused Bytes............................ 16,384Last Used Ext BlockId................... 240Last Used Block......................... 6PL/SQL procedure successfully completed.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000; Table created. SQL> select count(*) from test; COUNT(*) ---------- 5000 SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 26 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed. |
2. 删除数据,制造碎片
1 2 3 4 |
SQL> delete from test where mod(object_id,3)=1; 1662 rows deleted. SQL> commit; Commit complete. |
3. 发现表上面的碎片已经产生了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 25 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 1 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed. |
4. 把表move,整理碎片
1 2 |
SQL> alter table test move; Table altered. |
5.可以看到碎片整理结果,效果很好
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 17 Total Blocks............................ 24 Total Bytes............................. 196,608 Total MBytes............................ 0 Unused Blocks........................... 3 Unused Bytes............................ 24,576 Last Used Ext BlockId................... 264 Last Used Block......................... 5 PL/SQL procedure successfully completed. |
6. 下面我们来看shrink的结果,首先创建测试表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000; Table created. SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 26 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed. |
7.制造碎片
1 2 3 4 |
SQL> delete from test where mod(object_id,3)=1; 1662 rows deleted. SQL> commit; Commit complete. |
8. 查看已经存在碎片
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 25 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 1 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed. |
9. 使用shrink整理碎片
1 2 3 4 5 6 7 8 9 |
SQL> alter table test shrink space compact cascade; alter table test shrink space compact cascade * ERROR at line 1: ORA-10636: ROW MOVEMENT is not enabled SQL> alter table test enable row movement; Table altered. SQL> alter table test shrink space compact cascade; Table altered. |
10. 查看整理后的碎片,发现还有一些碎片,整理效果不是很好。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 1 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 9 Full Blocks ............................ 16 Total Blocks............................ 32 Total Bytes............................. 262,144 Total MBytes............................ 0 Unused Blocks........................... 2 Unused Bytes............................ 16,384 Last Used Ext BlockId................... 240 Last Used Block......................... 6 PL/SQL procedure successfully completed. |
11.然后在对其进行move,发现没有碎片,整理效果很好
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> alter table test move; Table altered. SQL> exec show_space('TEST'); Unformatted Blocks ..................... 0 FS1 Blocks (0-25) ...................... 0 FS2 Blocks (25-50) ..................... 0 FS3 Blocks (50-75) ..................... 0 FS4 Blocks (75-100)..................... 0 Full Blocks ............................ 17 Total Blocks............................ 24 Total Bytes............................. 196,608 Total MBytes............................ 0 Unused Blocks........................... 3 Unused Bytes............................ 24,576 Last Used Ext BlockId................... 264 Last Used Block......................... 5 PL/SQL procedure successfully completed. |
上述结论,虽然shrink和move都是通过物理调整rowid来整理碎片的,但shrink space整理不彻底。只是尽可能的合并。
二:下面我们来看一下move和shrink高水位的问题。
1. 创建测试表
1 2 |
SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000; Table created. |
2. 查看分配的空间
1 2 |
SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test'); no rows selected |
3. 忘了分析表,这里可以看到分配了3个extents,24个blocks
1 2 3 4 5 6 |
SQL> analyze table test compute statistics; Table analyzed. SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test'); SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 3 24 |
4. 已经使用20个blocks,还剩下4个blocks
1 2 3 4 |
SQL> select table_name,blocks,empty_blocks from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ------------ TEST 20 4 |
5. 下面删除一部分数据,再查看使用情况,可以看使用还是20个blocks
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> delete from test where rownum <=2000; 2000 rows deleted. SQL> commit; Commit complete. SQL> analyze table test compute statistics; Table analyzed. SQL> select segment_name,extents,blocks from user_segments where segment_name='TEST'; SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 3 24 SQL> select table_name,blocks,empty_blocks from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ------------ TEST 20 4 |
6. 实际只使用了12个blocks
1 2 3 4 |
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test; USED_BLOCKS ----------- 12 |
7. 下面对表进行move,降低高水位HWM,这里不要忘了analyze表哦。可以看到使用的blocks数已经下降,高水位已经回收
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> alter table test move; Table altered. SQL> analyze table test compute statistics; Table analyzed. SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST'; SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 2 16 SQL> select table_name,blocks,empty_blocks from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ------------ TEST 15 1 |
move操作后,数据的rowid发生了改变,而index是通过rowid来fetch数据行的,所以,table上的index是必须要rebuild
8. 下面看一下shrink对hwm的回收
1 2 3 4 5 6 7 |
SQL> delete from test where rownum <=2000; 2000 rows deleted. SQL> commit; Commit complete. |
9. delete之后,高水位还是在block 15上
1 2 3 4 5 6 7 8 9 10 |
SQL> analyze table test compute statistics; Table analyzed. SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST'; SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 2 16 SQL> select table_name,blocks,empty_blocks from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ------------ TEST 15 1 |
10.我们队表进行shrink后,可以看到分配的空间小了一半
123456789
SQL> alter table test enable row movement;Table altered.SQL> alter table test shrink space;Table altered.SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST';SEGMENT_NAME EXTENTS BLOCKS-------------------------------------------------------------------------------------------------------------------------------- ---------- ----------TEST 1 8alter table <table_name> shrink space [ <null> | compact | cascade ];
1 2 3 4 5 6 7 8 9 |
SQL> alter table test enable row movement; Table altered. SQL> alter table test shrink space; Table altered. SQL> select segment_name , extents,blocks from user_segments where segment_name ='TEST'; SEGMENT_NAME EXTENTS BLOCKS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- TEST 1 8 alter table <table_name> shrink space [ <null> | compact | cascade ]; |
compact:这个参数当系统的负载比较大时可以用,不降低HWM。如果系统负载较低时,直接用alter table table_name shrink space就一步到位了
cascade:这个参数是在shrink table的时候自动级联索引,相当于rebulid index。
shrink必须开启行迁移功能。
alter table table_name enable row movement ;
保持HWM,相当于把块中数据打结实了
alter table table_name shrink space compact;
回缩表与降低HWM
alter table table_name shrink space;
回缩表与相关索引,降低HWM
alter table table_name shrink space cascade;
回缩表与相关所以,报错HWM
alter table table_name shrink space compact cascade;
回缩索引与降低HWM
alter index index_name shrink space
虽然在10g中可以用shrink ,但也有些限制:
1). 对cluster,cluster table,或具有Long,lob类型列的对象 不起作用。
2). 不支持具有function-based indexes 或 bitmap join indexes的表
3). 不支持mapping 表或index-organized表。
4). 不支持compressed 表
下面简单看下move后索引失效:
1 2 3 4 5 6 |
SQL> create index test_idx on test(object_id); Index created. SQL> select index_name,status from user_indexes; INDEX_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX VALID |
然后对表进行move,索引已经失效了
1 2 3 4 5 6 |
SQL> alter table test move; Table altered. SQL> select index_name,status from user_indexes; INDEX_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX UNUSABLE |
下面对索引进行rebuild后,索引生效
123456
SQL> alter index test_idx rebuild;Index altered.SQL> select index_name,status from user_indexes;INDEX_NAME STATUS-------------------------------------------------------------------------------------------------------------------------------- --------TEST_IDX VALID
1 2 3 4 5 6 |
SQL> alter index test_idx rebuild; Index altered. SQL> select index_name,status from user_indexes; INDEX_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX VALID |
测试一下shrink之后,索引的状态,可以看到shrink后,索引依然生效
1 2 3 4 5 6 |
SQL> alter table test shrink space; Table altered. SQL> select index_name,status from user_indexes; INDEX_NAME STATUS -------------------------------------------------------------------------------------------------------------------------------- -------- TEST_IDX VALID |
- 本文固定链接: http://www.savedba.com/?p=103
- 转载请注明: 版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!