Alter table move and shrink space

We all know that alter table move or shrink space can contract segments to eliminate part of row migration and space fragments, so that data is closer. However, moving and shrink space are different.
Move will Move the high water level, but will not release the applied space. It is the operation below the high water level (below HWM.
Shrink space will also move the high water level, but it will also release the applied space, which is the operation of below and above HWM.
It may be difficult to understand. You can see the test.
 
SQL> select * from v $ version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
 
SQL> create table test (id number) storage (initial 10 m next 1 m) tablespace users;
 
Table created.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS, INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS INIT
----------------------------------------
TEST 10 1280 10
 
SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 0 1280
The -- TEST table is initially allocated with 10 MB space. We can see that there are 10 EXTENTS and 1280 BLOCKS. The USER_TABLES view displays 0 BLOCKS and 1280 idle BLOCKS. That is, the BLOCKS in the 10 MB space are not formatted by ORACLE ".
 
SQL> begin
2 for I in 1 .. 100000 loop
3 insert into test values (I );
4 end loop;
5 end;
6/
 
PL/SQL procedure successfully completed.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS from user_segments where SEGMENT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS
------------------------------
TEST 10 1280
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 186 1094
-- After 10 million data records are inserted, the allocated space remains unchanged because 10 EXTENTS are not used up. 186 BLOCKS and 1094 BLOCKS are used. At this time, 186BLOCKS is a high-water line.
 
SQL> delete from test where rownum <= 50000;
 
50000 rows deleted.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS from user_segments where SEGMENT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS
------------------------------
TEST 10 1280
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 186 1094
 
SQL> select count (distinct dbms_rowid.rowid_block_number (rowid) used_blocks from test;
 
USED_BLOCKS
-----------
77
-- We can see that after half of the data is deleted, 186 BLOCKS are still used, and the high water level remains unchanged. However, only 77 blocks are actually used in the query. Therefore, the DELETE operation will not change the HWM.
 
SQL> alter table test move;
 
Table altered.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 81 1199
-- After moving, the HWM is reduced and the idle block is also increased.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS from user_segments where SEGMENT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS
------------------------------
TEST 10 1280
-- But the allocated space has not changed. It is still 1280 BLOCKS. The shrink space method is as follows:
 
SQL> alter table test enable row movement;
 
Table altered.
 
SQL> alter table test shrink space;
 
Table altered.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS from user_segments where SEGMENT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS
------------------------------
TEST 1 88
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 81 7
-- The allocated space has been minimized, with 1 EXTENTS and 88 BLOCKS.
 
 
Therefore, moving does not really compress the space. It only compresses the space below HWM to eliminate fragments. We generally do not specify the initial parameter (eight blocks by default) when creating a table, so we do not feel this difference. Shrink space actually compresses segments, including initial allocation, so it is a blow and above HWM operation.
As to which method is needed, it depends on your requirements and the growth of the analysis table. If it will reach the previous HWM height in the future, it is obvious that moving is more appropriate, because shrink space still needs to apply for the SPACE that was previously released, operations are undoubtedly added.
 
Note:
1. However, you can use the MOVE method to compress and allocate space. You only need to specify the STORAGE parameter.
 
SQL> drop table test;
 
Table dropped.
 
SQL> create table test (id number) storage (initial 10 m next 1 m) tablespace users;
 
Table created.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS, INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS INIT
----------------------------------------
TEST 10 1280 10
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 0 1280
 
SQL> alter table test move storage (initial 1 m );
 
Table altered.
 
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select SEGMENT_NAME, EXTENTS, BLOCKS, INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME = 'test ';
 
SEGMENT_NA EXTENTS BLOCKS INIT
----------------------------------------
TEST 16 128 1
 
SQL> select TABLE_NAME, BLOCKS, EMPTY_BLOCKS from user_tables where table_name = 'test ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------
TEST 0 128
 
2. When moving is used, the ROWID of some records is changed. Therefore, after moving, the index becomes invalid and requires REBUILD.
3. indexes are automatically maintained when shrink space is used. If compression is performed during busy business hours, shrink space compact can be used to compress the data without moving the HWM. Then, when not busy, shrink space can be used to move the HWM.
4. indexes can also be compressed. Specifying Shrink space cascade during table compression compresses indexes at the same time. You can also alter index xxx shrink space to compress indexes.
5. The shrink space must be automatically managed in the tablespace, so the table on the system tablespace cannot be shrink space.