oracle enable rowmove,Oracle Alter table move 和 shrink的区别。

move 和 shrink都会移动表的高水位，但不同的是move不会释放已经申请的空间，move的时候会锁表，而且move会后索引会失效，shrink后会释放申请的空间，而且不会造成索引失效。此外碎片的整理也不一致。

使用shrink的时候要注意，表要开启行迁移enable row movement，且表段所在表空间的段空间管理(segment space management)必须为auto，所以system表空间上面的无法shrink，

PgSQL

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

2

3

4

5

6

7

SQL>select*fromv$version;

BANNERCON_ID

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

OracleDatabase12cEnterpriseEditionRelease12.1.0.1.0-64bitProduction0

PL/SQLRelease12.1.0.1.0-Production0CORE12.1.0.1.0Production0

TNSforLinux:Version12.1.0.1.0-Production0

NLSRTLVersion12.1.0.1.0-Production0

一. 首先我们来看一下碎片的整理情况

1. 建立测试表

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

SQL>createtabletestasselectobject_id,object_namefromdba_objectswhererownum<=5000;

Tablecreated.

SQL>selectcount(*)fromtest;

COUNT(*)

----------

5000

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................0

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................26

TotalBlocks............................32

TotalBytes.............................262,144

TotalMBytes............................0

UnusedBlocks...........................2

UnusedBytes............................16,384

LastUsedExtBlockId...................240

LastUsedBlock.........................6

PL/SQLproceduresuccessfullycompleted.

2. 删除数据，制造碎片

PgSQL

SQL> delete from test where mod(object_id,3)=1;

1662 rows deleted.

SQL> commit;

Commit complete.

1

2

3

4

SQL>deletefromtestwheremod(object_id,3)=1;

1662rowsdeleted.

SQL>commit;

Commitcomplete.

3. 发现表上面的碎片已经产生了

C#

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................25

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................1

TotalBlocks............................32

TotalBytes.............................262,144

TotalMBytes............................0

UnusedBlocks...........................2

UnusedBytes............................16,384

LastUsedExtBlockId...................240

LastUsedBlock.........................6

PL/SQLproceduresuccessfullycompleted.

4. 把表move，整理碎片

PgSQL

SQL> alter table test move;

Table altered.

1

2

SQL>altertabletestmove;

Tablealtered.

5.可以看到碎片整理结果，效果很好

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................0

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................17

TotalBlocks............................24

TotalBytes.............................196,608

TotalMBytes............................0

UnusedBlocks...........................3

UnusedBytes............................24,576

LastUsedExtBlockId...................264

LastUsedBlock.........................5

PL/SQLproceduresuccessfullycompleted.

6. 下面我们来看shrink的结果，首先创建测试表

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL>createtabletestasselectobject_id,object_namefromdba_objectswhererownum<=5000;

Tablecreated.

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................0

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................26

TotalBlocks............................32

TotalBytes.............................262,144

TotalMBytes............................0

UnusedBlocks...........................2

UnusedBytes............................16,384

LastUsedExtBlockId...................240

LastUsedBlock.........................6

PL/SQLproceduresuccessfullycompleted.

7.制造碎片

PgSQL

SQL> delete from test where mod(object_id,3)=1;

1662 rows deleted.

SQL> commit;

Commit complete.

1

2

3

4

SQL>deletefromtestwheremod(object_id,3)=1;

1662rowsdeleted.

SQL>commit;

Commitcomplete.

8. 查看已经存在碎片

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................25

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................1

TotalBlocks............................32

TotalBytes.............................262,144

TotalMBytes............................0

UnusedBlocks...........................2

UnusedBytes............................16,384

LastUsedExtBlockId...................240

LastUsedBlock.........................6

PL/SQLproceduresuccessfullycompleted.

9. 使用shrink整理碎片

PgSQL

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.

1

2

3

4

5

6

7

8

9

SQL>altertabletestshrinkspacecompactcascade;

altertabletestshrinkspacecompactcascade

*

ERRORatline1:

ORA-10636:ROWMOVEMENTisnotenabled

SQL>altertabletestenablerowmovement;

Tablealtered.

SQL>altertabletestshrinkspacecompactcascade;

Tablealtered.

10. 查看整理后的碎片，发现还有一些碎片，整理效果不是很好。

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................1

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................9

FullBlocks............................16

TotalBlocks............................32

TotalBytes.............................262,144

TotalMBytes............................0

UnusedBlocks...........................2

UnusedBytes............................16,384

LastUsedExtBlockId...................240

LastUsedBlock.........................6

PL/SQLproceduresuccessfullycompleted.

11.然后在对其进行move，发现没有碎片，整理效果很好

PgSQL

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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

SQL>altertabletestmove;

Tablealtered.

SQL>execshow_space('TEST');

UnformattedBlocks.....................0

FS1Blocks(0-25)......................0

FS2Blocks(25-50).....................0

FS3Blocks(50-75).....................0

FS4Blocks(75-100).....................0

FullBlocks............................17

TotalBlocks............................24

TotalBytes.............................196,608

TotalMBytes............................0

UnusedBlocks...........................3

UnusedBytes............................24,576

LastUsedExtBlockId...................264

LastUsedBlock.........................5

PL/SQLproceduresuccessfullycompleted.

上述结论，虽然shrink和move都是通过物理调整rowid来整理碎片的，但shrink space整理不彻底。只是尽可能的合并。

二：下面我们来看一下move和shrink高水位的问题。

1. 创建测试表

PgSQL

SQL> create table test as select object_id,object_name from dba_objects where rownum <=5000;

Table created.

1

2

SQL>createtabletestasselectobject_id,object_namefromdba_objectswhererownum<=5000;

Tablecreated.

2. 查看分配的空间

C#

SQL> select us.segment_name,us.extents,us.blocks from user_segments us where us.segment_name=upper('test');

no rows selected

1

2

SQL>selectus.segment_name,us.extents,us.blocksfromuser_segmentsuswhereus.segment_name=upper('test');

norowsselected

3. 忘了分析表，这里可以看到分配了3个extents，24个blocks

PgSQL

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

1

2

3

4

5

6

SQL>analyzetabletestcomputestatistics;

Tableanalyzed.

SQL>selectus.segment_name,us.extents,us.blocksfromuser_segmentsuswhereus.segment_name=upper('test');

SEGMENT_NAMEEXTENTSBLOCKS

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

TEST324

4. 已经使用20个blocks，还剩下4个blocks

PgSQL

SQL> select table_name,blocks,empty_blocks from user_tables where table_name='TEST';

TABLE_NAME BLOCKS EMPTY_BLOCKS

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

TEST 20 4

1

2

3

4

SQL>selecttable_name,blocks,empty_blocksfromuser_tableswheretable_name='TEST';

TABLE_NAMEBLOCKSEMPTY_BLOCKS

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

TEST204

5. 下面删除一部分数据，再查看使用情况，可以看使用还是20个blocks

PgSQL

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SQL>deletefromtestwhererownum<=2000;

2000rowsdeleted.

SQL>commit;

Commitcomplete.

SQL>analyzetabletestcomputestatistics;

Tableanalyzed.

SQL>selectsegment_name,extents,blocksfromuser_segmentswheresegment_name='TEST';

SEGMENT_NAMEEXTENTSBLOCKS

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

TEST324

SQL>selecttable_name,blocks,empty_blocksfromuser_tableswheretable_name='TEST';

TABLE_NAMEBLOCKSEMPTY_BLOCKS

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

TEST204

6. 实际只使用了12个blocks

PgSQL

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;

USED_BLOCKS

-----------

12

1

2

3

4

SQL>selectcount(distinctdbms_rowid.rowid_block_number(rowid))used_blocksfromtest;

USED_BLOCKS

-----------

12

7. 下面对表进行move，降低高水位HWM，这里不要忘了analyze表哦。可以看到使用的blocks数已经下降，高水位已经回收

PgSQL

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

1

2

3

4

5

6

7

8

9

10

11

12

SQL>altertabletestmove;

Tablealtered.

SQL>analyzetabletestcomputestatistics;

Tableanalyzed.

SQL>selectsegment_name,extents,blocksfromuser_segmentswheresegment_name='TEST';

SEGMENT_NAMEEXTENTSBLOCKS

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

TEST216

SQL>selecttable_name,blocks,empty_blocksfromuser_tableswheretable_name='TEST';

TABLE_NAMEBLOCKSEMPTY_BLOCKS

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

TEST151

move操作后，数据的rowid发生了改变，而index是通过rowid来fetch数据行的，所以，table上的index是必须要rebuild

8. 下面看一下shrink对hwm的回收

PgSQL

SQL> delete from test where rownum <=2000;

2000 rows deleted.

SQL> commit;

Commit complete.

1

2

3

4

5

6

7

SQL>deletefromtestwhererownum<=2000;

2000rowsdeleted.

SQL>commit;

Commitcomplete.

9. delete之后，高水位还是在block 15上

PgSQL

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

1

2

3

4

5

6

7

8

9

10

SQL>analyzetabletestcomputestatistics;

Tableanalyzed.

SQL>selectsegment_name,extents,blocksfromuser_segmentswheresegment_name='TEST';

SEGMENT_NAMEEXTENTSBLOCKS

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

TEST216

SQL>selecttable_name,blocks,empty_blocksfromuser_tableswheretable_name='TEST';

TABLE_NAMEBLOCKSEMPTY_BLOCKS

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

TEST151

10.我们队表进行shrink后，可以看到分配的空间小了一半

PgSQL

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 shrink space [ | compact | cascade ];

1

2

3

4

5

6

7

8

9

SQL>altertabletestenablerowmovement;

Tablealtered.

SQL>altertabletestshrinkspace;

Tablealtered.

SQL>selectsegment_name,extents,blocksfromuser_segmentswheresegment_name='TEST';

SEGMENT_NAMEEXTENTSBLOCKS

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

TEST18

altertableshrinkspace[|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后索引失效：

PgSQL

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

1

2

3

4

5

6

SQL>createindextest_idxontest(object_id);

Indexcreated.

SQL>selectindex_name,statusfromuser_indexes;

INDEX_NAMESTATUS

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

TEST_IDXVALID

然后对表进行move，索引已经失效了

PgSQL

SQL> alter table test move;

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

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

TEST_IDX UNUSABLE

1

2

3

4

5

6

SQL>altertabletestmove;

Tablealtered.

SQL>selectindex_name,statusfromuser_indexes;

INDEX_NAMESTATUS

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

TEST_IDXUNUSABLE

下面对索引进行rebuild后，索引生效

PgSQL

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>alterindextest_idxrebuild;

Indexaltered.

SQL>selectindex_name,statusfromuser_indexes;

INDEX_NAMESTATUS

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

TEST_IDXVALID

测试一下shrink之后，索引的状态，可以看到shrink后，索引依然生效

PgSQL

SQL> alter table test shrink space;

Table altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME STATUS

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

TEST_IDX VALID

1

2

3

4

5

6

SQL>altertabletestshrinkspace;

Tablealtered.

SQL>selectindex_name,statusfromuser_indexes;

INDEX_NAMESTATUS

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

TEST_IDXVALID

转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

最后编辑：2014-01-25作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL