We first need to understand the mechanism of Move: http://blog.9cbs.net/dlinger/archive/2004/07/12/39667.aspx now look at the TABLESPACE information:
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 version information:
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. Archiving mode: segment approx
SQL> CREATE TABLE MY_OBJECTS TABLESPACE ASSM AS
Select * from all_Objects where rownum <2000;
Table created
SQL> delete from my_objects where object_name like '% C%';
7278 ROWS DELETED
SQL> commit;
Commit completion
SQL> delete from my_objects where object_name like '% u%';
2732 ROWS DELETED
SQL> commit;
Commit completion
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 about 1m
2. Archive mode: Segment FLM
SQL> CREATE TABLE MY_OBJECTS TABLESPACE HWM
As SELECT * 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 completion
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
We found that in archiving mode, Segment regardless of the ASSM or FLM, the log generated by Move is the same.
3. Non-archive mode: Segment ASSM
SQL> CREATE TABLE MY_OBJECTS TABLESPACE ASSM
As SELECT * 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 completion
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 about 250K
4. Non-archive mode: Segment FLM
SQL> CREATE TABLE MY_OBJECTS TABLESPACE HWM
As SELECT * 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 completion
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 about 80K
Through these experiments, we found that the Redo size generated in the non-invasive mode is more than the archive mode, and the redo size, which is generated under the FLM, in the non-archive mode, in the non-invasive mode. There is less.
Archive mode non-archive mode
FLM
1M
About 80K
ASSM
1M
About 250K
5. Let's take a look at the situation in archiving mode for nologging, here is used to test with FLM:
SQL> CREATE TABLE MY_OBJECTS TABLESPACE HWM AS SELECT * FROM
All_Objects where rownum <2000;
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 completion
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 about 80K
At this time, the generation amount of the Redo size is the same as FLM of the non-archive mode.
We have found that the log generated by the Table Move operation is very similar to the fact that the archive and non-archive mode and the result of the Redo size under NOLogging are very similar.
http://blog.9cbs.net/dlinger/archive/
2004/07/14
/ 41359.aspx
http://www.itpub.net/showthread.php?threadid=242761
For log generation comparisons about Move and Shrink, see:
http://blog.9cbs.net/dlinger/archive/
2004/07/14
/ 41364.aspx
