ITPub博客

Moving表到新的段或者表空间

原创 Oracle 作者:尛样儿 时间:2015-08-09 21:46:09 1 删除 编辑

这篇文章继续前两篇文章的内容,讨论Oracle段碎片整理的相关话题,相关文章:
   《使用在线段收缩(ONLINE SEGMENT SHRINK)回收浪费的段空间》:http://blog.itpub.net/23135684/viewspace-1765511/
   《使用DBMS_REDEFINITION包执行在线重定义表(ONLINE TABLE REDEFINITION)》:http://blog.itpub.net/23135684/viewspace-1765128/

    ALTER TABLE ...MOVE语句能够移动非分区表或者分区表的一个分区的数据到新的段,和任意有权限的不同表空间。这个语句也可以修改表或者分区任何的存储参数(Storage),包括不能通过ALTER TABLE修改的参数,也可以使用ALTER TABLE ... MOVE语句加上COMPRESS字句使用表压缩存储新的段。

当ALTER TABLE ... MOVE语句在执行的时候不允许在这个表上有DML语句执行,如果需要在Moving期间允许DML操作需要使用在线重定义表技术。

以下的语句移动hr.admin_emp表到一个新段,指定新的存储参数:
ALTER TABLE hr.admin_emp MOVE
      STORAGE ( INITIAL 20K
                NEXT 40K
                MINEXTENTS 2
                MAXEXTENTS 20
                PCTINCREASE 0 );

    Moving表改变了表行的rowid号,这会引起表上的索引被标记为UNUSABLE,任何使用索引的DML语句访问这个表将收到ORA-01502的错误,表上的索引必须被DROP或者重建,同样的,表上的任何统计信息都变得无效,在Moving表完成之后应该收集新的统计信息。

    如果表包含LOB字段,在用户明确指定的情况下,这个语句能用于移动和这个表相关的LOB数据段,LOB索引段,如果没有指定,默认是不移动LOB数据和LOB索引段。


下面通过一个简单测试来讨论一下Moving Table和Online Segment SHRINK的区别:

1.创建测试数据:
SQL> connect test1/test1

表已删除。

SQL> create table test123 (id number primary key,name char(2000));

表已创建。

SQL> begin
  2  for i in 1..10000 loop
  3  insert into test123 values(i ,i||'abc');
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> commit;

提交完成。

创建了10000条测试数据。

2.显示TEST1.TEST123数据分布情况。

SQL> connect / as sysdba
已连接。
SQL> analyze table test1.test123 compute statistics;

表已分析。

SQL> set serveroutput on
SQL> exec show_space('TEST123','AUTO','T','Y','TEST1');
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................4864
Last Used Block.........................128
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........30
75% -- 100% free space bytes............245760
Unused Blocks...........................30
Unused Bytes............................245760
Total Blocks............................3333
Total bytes.............................27303936

PL/SQL 过程已成功完成。

3.删除部分数据。

SQL> begin
  2  for i in 1..2000 loop
  3  delete from test1.test123 where id =(select ABS(MOD(DBMS_RANDOM.RANDOM,10000)) from dual);
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> commit;

提交完成。

随机删除了2000条数据。

4.获得删除后的数据分布。

SQL> analyze table test1.test123 compute statistics;

表已分析。

SQL> exec show_space('TEST123','AUTO','T','Y','TEST1');
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................4864
Last Used Block.........................128
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............1199
25% -- 50% free space bytes.............9822208
50% -- 75% free space blocks............273
50% -- 75% free space bytes.............2236416
75% -- 100% free space blocks...........49
75% -- 100% free space bytes............401408
Unused Blocks...........................30
Unused Bytes............................245760
Total Blocks............................1843
Total bytes.............................15097856

PL/SQL 过程已成功完成。

随机删除了2000条数据后,出现了很多未填满的块,高水位线未变化。

5.执行ONLINE SEGMENT SHRINK操作。

SQL> alter table test1.test123 enable row movement;

表已更改。

SQL> alter table test1.test123 shrink space cascade;

表已更改。

SQL> analyze table test1.test123 compute statistics;

表已分析。

SQL> exec show_space('TEST123','AUTO','T','Y','TEST1');
Total Blocks............................2792
Total Bytes.............................22872064
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................4
Last Used Ext BlockId...................4224
Last Used Block.........................98
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............1
0% -- 25% free space bytes..............8192
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................2732
Total bytes.............................22380544

PL/SQL 过程已成功完成。

SHRINK表之后,表碎片得到了整理,高水位线下降,未填满的块减少了非常多,效果很理想。

6.执行Moving表操作。

SQL> alter table test1.test123 move;

表已更改。

SQL> analyze table test1.test123 compute statistics;

表已分析。

SQL> exec show_space('TEST123','AUTO','T','Y','TEST1');
Total Blocks............................2816
Total Bytes.............................23068672
Unused Blocks...........................30
Unused Bytes............................245760
Last Used Ext FileId....................4
Last Used Ext BlockId...................7040
Last Used Block.........................98
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................2734
Total bytes.............................22396928

PL/SQL 过程已成功完成。

    首先,对表的Move操作是能够实现碎片整理的效果(和SHRINK效果类似);Move表之后,Table中的数据得到了重新组织,已经没有了任何未填满的块,所以单从碎片整理的角度来说,Moving的效果要好于SHRINK,原因是Moving操作是在新的段上重新组织数据,表中数据的连续性,紧密型性都要好于SHRINK操作(把数据压得更紧),move后段尾出现了一些unused的块,这是因为extent分配的大小是固定的,下面执行一下deallocate操作。

7.执行DEALLOCATE操作。

SQL> alter table test1.test123 deallocate unused;

表已更改。

SQL> analyze table test1.test123 compute statistics;

表已分析。

SQL> exec show_space('TEST123','AUTO','T','Y','TEST1');
Total Blocks............................2792
Total Bytes.............................22872064
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................4
Last Used Ext BlockId...................7040
Last Used Block.........................98
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................2734
Total bytes.............................22396928

PL/SQL 过程已成功完成。

执行deallocate之后,段尾的unused block得到释放,高水位线和SHRINK操作之后的一致。

    虽然MOVE TABLE在碎片整理方面比ONLINE SEGMENT SHRINK的效果好,但是MOVE TABLE期间不能执行任何DML操作,执行后索引会失效,统计信息会失效,这些方面要比ONLINE SEGMENT SHRINK弱很多,在允许停机和数据量不大的情况下,可以考虑使用Move Table来整理SEGMENT的碎片,对于高可用的数据库ONLINE SEGMENT SHRINK更适合。

--end--

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23135684/viewspace-1766480/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle数据库管理员,Oracle数据库系统构架员;2012年7月出版《构建最高可用Oracle数据库系统:Oracle 11gR2 RAC管理、维护与性能优化》一书;Oracle 10g OCM。

注册时间:2010-01-05

  • 博文量
    483
  • 访问量
    5204764