通过shrink操作重整表的实验

实验目的：
1）shrink 操作是否产生redo。
2）shrink操作PK重建表

实验一：shrink 操作是否产生redo
SQL>conn scott/tiger
SQL> set serveroutput on
SQL> EXEC show_space_assm('TEST2','SCOTT');
free space 0-25% Blocks:................0                                       
free space 25-50% Blocks:...............260                                    
free space 50-75% Blocks:...............145                                    
free space 75-100% Blocks:..............81                                      
Full Blocks:............................9958                                    
Unformatted blocks:.....................0                                       

PL/SQL 过程已成功完成。

由上输出可以知道这个表存在shrink的余地。
SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
redo size                                                               664     

SQL> alter table test2 enable row movement;

表已更改。

SQL> alter table test2 shrink space cascade;

表已更改。

SQL> select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
redo size                                                          15481444     

SQL> EXEC show_space_assm('TEST2','SCOTT');
free space 0-25% Blocks:................2
free space 25-50% Blocks:...............2
free space 50-75% Blocks:...............0
free space 75-100% Blocks:..............0
Full Blocks:............................10155
Unformatted blocks:.....................0

PL/SQL 过程已成功完成。

SQL> spool off

由于是我的测试库，只有我一个会话当前。由实验可得出结论：shrink操作会产生redo记录。这个实验中产生了（15481444-664/）/1024/1024=15M的日志。

实验二：shrink表以后，空间的利用率跟重建表哪个操作更佳。
SQL> EXEC show_space_assm('TEST1','SCOTT');
free space 0-25% Blocks:................0                                       
free space 25-50% Blocks:...............0                                       
free space 50-75% Blocks:...............1                                       
free space 75-100% Blocks:..............0                                       
Full Blocks:............................10338                                   
Unformatted blocks:.....................0                                       

PL/SQL 过程已成功完成。

SQL> SELECT blocks FROM dba_tables WHERE table_name='TEST1';

    BLOCKS                                                                     
----------                                                                     
     10339                                                                     
shrink前，此表的块数十10339
SQL> delete from test1 where object_id like '14%';

已删除17760行。

SQL> commit;


SQL> EXEC show_space_assm('TEST1','SCOTT');

PL/SQL 过程已成功完成。

SQL> EXEC show_space_assm('TEST1','SCOTT');
free space 0-25% Blocks:................51                                      
free space 25-50% Blocks:...............179                                    
free space 50-75% Blocks:...............127                                    
free space 75-100% Blocks:..............96                                      
Full Blocks:............................9886                                    
Unformatted blocks:.....................0                                       

PL/SQL 过程已成功完成。


SQL> alter table test1 enable row movement;

表已更改。

SQL> alter table test1 shrink space cascade;

表已更改。


SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
  3                                  TABNAME    => 'TEST1',
  4                                  CASCADE    => TRUE,
  5                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE auto');
  6  END;
  7  /

PL/SQL 过程已成功完成。

SQL> SELECT blocks FROM dba_tables WHERE table_name='TEST1';

    BLOCKS                                                                     
----------                                                                     
     10064                                                                     

shrink后，此表的块数下降到10064。
看看重建表的块数是多少。
SQL> create table test2 as select * from test1;

表已创建。

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',
  3                                  TABNAME    => 'TEST2',
  4                                  CASCADE    => TRUE,
  5                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE auto');
  6  END;
  7  /

PL/SQL 过程已成功完成。

SQL> SELECT blocks FROM dba_tables WHERE table_name='TEST2';

    BLOCKS                                                                     
----------                                                                     
     10340                                                                     

SQL> set serveroutput on
SQL> EXEC show_space_assm('TEST2','SCOTT');
free space 0-25% Blocks:................0                                       
free space 25-50% Blocks:...............0                                       
free space 50-75% Blocks:...............0                                       
free space 75-100% Blocks:..............0                                       
Full Blocks:............................10193                                   
Unformatted blocks:.....................0                                       

PL/SQL 过程已成功完成。

SQL> spool off

从实验看出：
shrink后，块的数量从10339降低到了10064。
重新建表后，块的数量为10340。
看来shrink操作重整表还是蛮不错的，不过日志量还是蛮大的。


还有点没想明白。为什么重建表后，竟然会比shrink前还大呢？

重建后10340>shrink前的10339.

我以为是create table test2 as。。。。。 语句的问题。
浴室我把test2表shrink，重新分析表test2后，还是10340大小。
在这一点上有点晕。
不过我又执行了一下show_space
SQL> EXEC show_space_assm('TEST2','SCOTT');
free space 0-25% Blocks:................0                                       
free space 25-50% Blocks:...............0                                       
free space 50-75% Blocks:...............0                                       
free space 75-100% Blocks:..............0                                       
Full Blocks:............................10193                                   
Unformatted blocks:.....................0                                       

为什么show_space和user_tables的block相差这么多呢？

[ 本帖最后由 wei-xh 于 2010-5-2 20:48 编辑 ]

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

转载于:http://blog.itpub.net/22034023/viewspace-662235/