我们首先需要了解一下move的机制:
现在看看tablespace的信息:
SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name in('ASSM','HWM');
TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT
------------------------------ ------------------------
ASSM AUTO
HWM MANUAL
Oracle版本信息:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0 .2.0 - Prod
PL/SQL Release 10.1.0 .2.0 - Production
CORE 10.1.0 .2.0 Production
TNS for 32-bit Windows: Version 10.1.0 .2.0 - Production
NLSRTL Version 10.1.0 .2.0 – Production
1.归档模式下: segment ASSM
SQL> create table my_objects tablespace ASSM as
select * from all_objects where rownum<20000;
Table created
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> commit;
Commit complete
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
5991164
SQL> alter table my_objects move;
Table altered
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
7220956
SQL> select 7220956-5991164 from dual;
7220956-5991164
---------------
1229792
redo size = 1229792 约 1M
2.归档模式下:segment FLM
SQL> create table my_objects tablespace HWM
as select * from all_objects where rownum<20000;
Table created
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
5929756
SQL> alter table my_objects move;
Table altered
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
7146592
SQL> select 7146592 - 5929756 from dual;
7146592-5929756
---------------
1216836
Redo size = 1216836约 1M
我们发现,在归档模式下,segment不管是ASSM还是FLM,move产生的日志是一样多的。
3.非归档模式下:segment ASSM
SQL> create table my_objects tablespace ASSM
as select * from all_objects where rownum<20000;
Table created
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
3754152
SQL> alter table my_objects move;
Table altered
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
4020876
SQL> select 4020876-3754152 from dual;
4020876-3754152
---------------
266724
Redo size = 266724 约250K
4.非归档模式下:segment FLM
SQL> create table my_objects tablespace HWM
as select * from all_objects where rownum<20000;
Table created
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
7747688
SQL> alter table my_objects move;
Table altered
SQL> select value from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
VALUE
----------
7829084
SQL> select 7829084 - 7747688 from dual;
7829084-7747688
---------------
81396
Redo size = 81396 约80K
通过这几个实验,我们发现,对table进行move操作时,在非归档模式下生成的redo size要比归档模式下少的多;非归档模式下,在FLM下比ASSM下生成的redo size也要少一些.
归档模式 非归档模式
FLM 约 1M 约80K
ASSM 约 1M 约250K
5.我们再来看看在归档模式下table为nologging的情况,这里用FLM来测试:
SQL> create table my_objects tablespace HWM as select * from
all_objects where rownum<20000;
Table created
SQL> alter table my_objects nologging;
Table altered
SQL> delete from my_objects where object_name like '%C%';
7278 rows deleted
SQL> delete from my_objects where object_name like '%U%';
2732 rows deleted
SQL> commit;
Commit complete
SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
VALUE
----------
10874352
SQL> alter table my_objects move;
Table altered
SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
VALUE
----------
10955704
SQL> select 10955704-10874352 from dual;
10955704-10874352
-----------------
81352
Redo size = 81352 约80K
这时,redo size的生成量和非归档模式的FLM下一样了。
我们发现,table move操作生成的日志在归档和非归档模式以及nologging下的redo size的结果和insert append生成redo size的情况非常类似。
http://blog.csdn.net/dlinger/archive/ 2004/07/14 /41359.aspx
http://www.itpub.net/showthread.php?threadid=242761
关于move和shrink的日志生成对比,参见:
http://blog.csdn.net/dlinger/archive/ 2004/07/14 /41364.aspx