Compare alter table t move and alter table t shrink space in Oracle

Alter table t move and alter table t shrink space can both be used for CIDR and CIDR to reduce the HWM,
It can also be used to eliminate Row Chaining and Row Migration ),
But there are the following differences:
1) Using alter table move will contract the table to the initial size specified by the storage clause when creating the table. Using alter table shrink space is not subject to this restriction.
2) After you use alter table move, the index will be invalid and need to be rebuilt. Using alter table shrink space will not invalidate the index.
3) alter table shrink SPACE can be used only when the tablespace in which the table is located is automatic SEGMENT space MANAGEMENT (the segment space management auto clause is specified when the tablespace is created.
4) You can use alter table shrink space compact to fragment the table without adjusting HWM, and then call alter table shrink space again to release the space.
5) You can use alter table shrink space cascade to contract all indexes at the same time. This is equivalent to executing alter index shrink space at the same time.
 
 
In the following example, create a table T. When creating a table, the storage clause specifies that the initial table size is 5 MB,
The database block size is 8 KB, so it is equivalent to 5*1024/8 = 640 blocks.
With alter table move, the table can be reduced to a maximum of 640 blocks.
Use alter table shrink space.
 
 
Tony @ ORA11GR2> select * from v $ version;
 
BANNER
Bytes ------------------------------------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
 
Tony @ ORA11GR2> show parameter db_block_size
 
NAME TYPE VALUE
----------------------------------------------------------------------------------------
Db_block_size integer 8192
 
Tony @ ORA11GR2> column segment_name format a40;
Tony @ ORA11GR2> column table_name format a40;
Tony @ ORA11GR2> create table t storage (initial 5 m) as select * from all_objects;
 
Table created.
 
Tony @ ORA11GR2> exec dbms_stats.gather_table_stats (user, 'T ');
 
PL/SQL procedure successfully completed.
 
Tony @ ORA11GR2> select table_name, blocks, empty_blocks from user_tables where table_name = 'T ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
T 820 0
 
Tony @ ORA11GR2> select segment_name, extents, blocks, initial_extent from user_segments where segment_name = 'T ';
 
SEGMENT_NAME extents blocks INITIAL_EXTENT
--------------------------------------------------------------------------
T 7 896 5242880
 
Tony @ ORA11GR2> select count (distinct dbms_rowid.rowid_block_number (rowid) from t;
 
COUNT (DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID ))
---------------------------------------------------
804
 
Tony @ ORA11GR2> delete from t where rownum <50000;
 
49999 rows deleted.
 
Tony @ ORA11GR2> create index t_idx on t (object_id );
 
Index created.
 
Tony @ ORA11GR2> alter table t move;
 
Table altered.
 
Tony @ ORA11GR2> column index_name format a40;
Tony @ ORA11GR2> select index_name, status from user_indexes where table_name = 'T ';
 
INDEX_NAME STATUS
--------------------------------------------------------
T_IDX UNUSABLE
 
Tony @ ORA11GR2> alter index t_idx rebuild;
 
Index altered.
 
Tony @ ORA11GR2> exec dbms_stats.gather_table_stats (user, 'T ');
 
PL/SQL procedure successfully completed.
 
Tony @ ORA11GR2> select table_name, blocks, empty_blocks from user_tables where table_name = 'T ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
T 86 0
 
Tony @ ORA11GR2> select segment_name, extents, blocks, initial_extent from user_segments where segment_name = 'T ';
 
SEGMENT_NAME extents blocks INITIAL_EXTENT
--------------------------------------------------------------------------
T 5 640 5242880
 
Tony @ ORA11GR2> select count (distinct dbms_rowid.rowid_block_number (rowid) from t;
 
COUNT (DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID ))
---------------------------------------------------
82
 
Tony @ ORA11GR2> alter table t enable row movement;
 
Table altered.
 
Tony @ ORA11GR2> alter table t shrink space;
 
Table altered.
 
Tony @ ORA11GR2> select index_name, status from user_indexes where table_name = 'T ';
 
INDEX_NAME STATUS
--------------------------------------------------------
T_IDX VALID
 
Tony @ ORA11GR2> exec dbms_stats.gather_table_stats (user, 'T ');
 
PL/SQL procedure successfully completed.
 
Tony @ ORA11GR2> select table_name, blocks, empty_blocks from user_tables where table_name = 'T ';
 
TABLE_NAME BLOCKS EMPTY_BLOCKS
--------------------------------------------------------------
T 86 0
 
Tony @ ORA11GR2> select segment_name, extents, blocks, initial_extent from user_segments where segment_name = 'T ';
 
SEGMENT_NAME extents blocks INITIAL_EXTENT
--------------------------------------------------------------------------
T 1 88, 5242880
 
Tony @ ORA11GR2> select count (distinct dbms_rowid.rowid_block_number (rowid) from t;
 
COUNT (DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID ))
---------------------------------------------------
82
 
The example www.2cto.com below demonstrates that alter table t move and alter table t shrink space can be used to eliminate Row Chaining and Row Migration ).
Create a chained_rows table first.
Run the $ ORACLE_HOME/RDBMS/ADMIN/utlchain. SQL script to create the chained_rows table,
Then execute analyze table xxx list chained rows [into chained_rows],
If a row link or row migration exists, query chained_rows to find the row with the row link or row migration.
 
Tony @ ORA11GR2> drop table t purge;
 
Table dropped.
 
Tony @ ORA11GR2> create table t
2 (x int primary key,
3 y varchar2 (4000)
4 );
 
Table created.
 
Tony @ ORA11GR2> insert into t (x, y)
2 select rownum, rpad ('*', 148 ,'*')
3 from dual
4 connect by level <= 46;
 
46 rows created.
 
Tony @ ORA11GR2> update t set y = rpad ('*', 2000, '*') where x = 1;
 
1 row updated.
 
Tony @ ORA11GR2> analyze table t list chained rows;
 
Table analyzed.
 
Tony @ ORA11GR2> select count (*) from chained_rows;
 
COUNT (*)
----------
1
 
Tony @ ORA11GR2> alter table t enable row movement;
 
Table altered.
 
Tony @ ORA11GR2> alter table t shrink space;
 
Table altered.
 
Tony @ ORA11GR2> delete from chained_rows;
 
1 row deleted.
 
Tony @ ORA11GR2> analyze table t list chained rows;
 
Table analyzed.
 
Tony @ ORA11GR2> select count (*) from chained_rows;
 
COUNT (*)
----------
0
 
Tony @ ORA11GR2> update t set y = rpad ('*', 2000, '*') where x = 2;
 
1 row updated.
 
Tony @ ORA11GR2> analyze table t list chained rows;
 
Table analyzed.
 
Tony @ ORA11GR2> select count (*) from chained_rows;
 
COUNT (*)
----------
1
 
Tony @ ORA11GR2> alter table t move;
 
Table altered.
 
Tony @ ORA11GR2> delete from chained_rows;
 
1 row deleted.
 
Tony @ ORA11GR2> analyze table t list chained rows;
 
Table analyzed.
 
Tony @ ORA11GR2> select count (*) from chained_rows;
 
COUNT (*)
----------
0


From NowOrNever