Let's take a look at Move and Shrink mechanism:
http://blog.9cbs.net/dlinger/archive/2004/07/12/39667.aspx
Http://blog.9cbs.net/dlinger/archive/2004/07/15/42491.aspx
test environment:
Oracle: 10.1.0.2.0? OS: Win2000 Server?
Test in non-archive mode:
Regarding the problem of logs, we contracted two tables of the same amount of data and distribution, and the Redo Size that is generated under Move and Shrink (there is no INDEX on Table):
SQL> SELECT TABLESPACE_NAME, Segment_Space_Management from DBA_TABLESPACES
2 WHERE TABLESPACE_NAME IN ('ASSM', 'HWM');
TABLESPACE_NAME segment_space_management
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----
Assm auto
HWM? Manual
SQL> CREATE TABLE MY_OBJECTS? TABLESPACE ASSM As SELECT * ALL_OBJECTS WHERE ROWNUM <20000
Table created
SQL> CREATE TABLE MY_OBJECTS1 TABLESPACE HWM? AS SELECT * ALL_OBJECTS WHERE ROWNUM <20000
Table created
SQL> SELECT BYTES / 1024/1024 from user_segments where segment_name = 'my_Objects';
BYTES / 1024/1024
---------------
2.1875
SQL> delete from my_objects where object_name like '% C%';
7278 ROWS DELETED
SQL> delete from my_objects1 where object_name like '% C%';
7278 ROWS DELETED
SQL> delete from my_objects where object_name like '% u%';
2732 ROWS DELETED
SQL> delete from my_objects1 where object_name like '% u%';
2732 ROWS DELETED
SQL> commit;
Commit completion
SQL> ALTER TABLE MY_OBJECTS Enable Row Movement
Table altered
SQL> SELECT VALUE FROM V $ MyStat, V $ STATNAME
2 WHERE V $ mystat.statistic # = V $ statname.statistic #
3 and v $ statname.name = 'redo size';
Value
------------
27808792
SQL> ALTER TABLE MY_OBJECTS SHRINK SPACE; TABLE Altered
SQL> SELECT VALUE FROM V $ MyStat, V $ STATNAME
2 WHERE V $ mystat.statistic # = V $ statname.statistic #
3 and? V $ statname.name = 'redo size';
Value
------------
32579712
SQL> ALTER TABLE MY_OBJECTS1 MOVE;
Table altered
SQL> SELECT VALUE FROM V $ MyStat, V $ STATNAME
2 WHERE V $ mystat.statistic # = V $ statname.statistic #
3 and? V $ statname.name = 'redo size';
Value
------------
32676784
For Table My_Objects, Shrink, 32579712 - 27808792 = 4770920, about
4.5M
Redo; Move for Table My_Objects1, generated 32676784-32579712 =? 97072, REDO Size of about 95K. So, compared with Move, Shrink's log is much larger.
Move generated log quantity, in archiving and non-archive mode, specific information, reference:
http://blog.9cbs.net/dlinger/archive/2004/08/04/61874.aspx
Use logmnr to analyze Move and Shrink's operations:
Move:
SQL> SELECT OPERATION, COUNT (*) ?? from v $ logmnr_contents group by Operation;
Operation count (*)
----------------------------------------------------------
Commit 36
DDL 1
Delete 1
INTERNAL 137
START 37
Unsupported 46
Update 32
7 rows success
SHRINK:
SQL> SELECT OPERATION, Count (*) from v $ logmnr_contents group by Operation;
Operation count (*)
----------------------------------------------------------
Commit 171
DDL 1
Delete 6248
INSERT 6248
Internal 96
START 171Unsupported 33
Update 26
8 rows success
We see, in the operation of Shrink, more performance than Move, the operation of DELETE and INSERT,
This should be the reasons why Shrink generated by Redo Size.
So, in archiving mode, we do Shrink operations, the resulting redo size and non-archived,
Note that Shrink is not like Move, and it is speculated that this is also because Shrink is more cause.
Then. The status of Table Nologging should have no effect on Shrink's Redo Size.
