Using shrink space to shrink ORACLE data segments

Using shrink space to shrink ORACLE data segments

In Oracle, you can use alter table table_name shrink space to contract a table. There are two prerequisites for using shrink:

 1. Row movement must be enabled for a table.
 2. The segment space management of the tablespace where the table segment is located must be auto

The experiment is as follows:

-- Create a segment space management auto tablespace
SQL> Create tablespace ts_auto datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ ts_auto.dbf 'size 100 m
 Extent management local segment space management auto;

The tablespace has been created.

-- Recommended test table
SQL> Create TableTb_autoAs select * From dba_objects;

The table has been created.

-- View the number of chunks before shrink
SQL> select blocks from dba_segments where segment_name = 'tb _ auto ';

   Blocks                                                                    
----------                                                                    
      768                                                                   

-- After the data is deleted, the space usage remains unchanged.
SQL> Delete from tb_auto;

50390 rows have been deleted.

SQL> commit;

Submitted.

SQL> select blocks from dba_segments where segment_name = 'tb _ auto ';

   Blocks                                                                    
----------                                                                    
      768                                                                    

-- Contract directly, prompting that the row movement option must be enabled
SQL> ALTER TABLE tb_auto shrink space;
Alter table tb_auto shrink Space
*
Row 3 has an error:
ORA-10636: Row movement is not enabled

SQL> ALTER TABLE tb_auto enable row movement;

The table has been changed.

-- The contraction is successful, and the space has been released.
SQL> ALTER TABLE tb_auto shrink space;

The table has been changed.

SQL> select blocks from dba_segments where segment_name = 'tb _ auto ';

   Blocks                                                                    
----------                                                                    
        8                                                                    

-- Shrink cannot be executed on the segment of the tablespace of the segment space management manaual.
SQL> Create tablespace ts_manual datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ orcl \ ts_mannel.dbf' size 100 m
 Extent management local segment space management manual;

The tablespace has been created.

SQL> select tablespace_name, segment_space_management from dba_tablespaces;

Tablespace_name              Segmen                                         
------------------------------------                                         
System                       Manual                                         
Undotbs1                     Manual                                         
Sysaux