ALTER TABLE Tablename SHRINK SPACE Command : Online Segment Shrink for Tables, LOBs and IOTs

The ALTER TABLE ... SHRINK SPACE command was introduced in Oracle 10g to perform online segment shrinks for tables, LOBs and IOT overflow segments.

Shrink Space Examples

SQL> -- Enable row movement.
SQL> ALTER TABLE scott.emp enable row movement;

Table altered.

SQL> 
SQL> -- Recover space and amend the high water mark (HWM).
SQL> ALTER TABLE scott.emp SHRINK SPACE;

Table altered.

SQL> 
SQL> -- Recover space, but don't amend the high water mark (HWM).
SQL> 
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Table altered.

SQL> -- Recover space for the object and all dependant objects.
SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Table altered.

SQL> 

The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the high water mark (HWM) is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any dependent SQL statements will need to be re-parsed.

Other shrink commands of interest are displayed below.

-- Shrink a LOB segment (basicfile only until 21c).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

Identifiy Large Segments

The DBA|ALL|USER_SEGMENTS views can be used to identify large segments. The following example uses a top-n query to display the 20 largest segments.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 14 11:38:03 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pagesize 200 linesize 200
SQL> 
SQL> SET LINESIZE 200
SQL> COLUMN owner FORMAT A30
SQL> COLUMN segment_name FORMAT A30
SQL> COLUMN tablespace_name FORMAT A30
SQL> COLUMN size_mb FORMAT 99999999.00
SQL> 
SQL> SELECT *
  2  FROM   (SELECT owner,
  3                 segment_name,
  4                 segment_type,
  5                 tablespace_name,
  6                 ROUND(bytes/1024/1024,2) size_mb
  7          FROM   dba_segments
  8          ORDER BY 5 DESC)
  9  WHERE  ROWNUM <= 20;

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE                                           TABLESPACE_NAME                     SIZE_MB
------------------------------ ------------------------------ ------------------------------------------------------ ------------------------------ ------------
SYS                            IDL_UB1$                       TABLE                                                  SYSTEM                               392.00
SYS                            SYS_LOB0000007422C00005$$      LOBSEGMENT                                             SYSAUX                                88.19
MDSYS                          SYS_LOB0000072920C00006$$      LOBSEGMENT                                             SYSAUX                                72.13
MDSYS                          SYS_LOB0000064076C00006$$      LOBSEGMENT                                             SYSAUX                                56.19
SYS                            SYS_LOB0000007422C00004$$      LOBSEGMENT                                             SYSAUX                                48.19
SYS                            C_TOID_VERSION#                CLUSTER                                                SYSTEM                                48.00
SYS                            SOURCE$                        TABLE                                                  SYSTEM                                38.00
SYS                            I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX                                                  SYSAUX                                32.00
SYS                            IDL_UB2$                       TABLE                                                  SYSTEM                                30.00
SYS                            SYS_LOB0000066421C00004$$      LOBSEGMENT                                             SYSAUX                                24.19
SYS                            JAVA$MC$                       TABLE                                                  SYSTEM                                19.00
SYS                            I_WRI$_OPTSTAT_H_ST            INDEX                                                  SYSAUX                                17.00
SYS                            SYS_LOB0000011147C00038$$      LOBSEGMENT                                             SYSAUX                                16.19
MDSYS                          SYS_LOB0000067342C00002$$      LOBSEGMENT                                             SYSAUX                                16.19
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY   TABLE PARTITION                                        SYSAUX                                16.00
SYS                            C_OBJ#_INTCOL#                 CLUSTER                                                SYSTEM                                16.00
SYS                            C_OBJ#                         CLUSTER                                                SYSTEM                                15.00
SYS                            SYS_LOB0000001362C00003$$      LOBSEGMENT                                             SYSTEM                                13.00
SYS                            ARGUMENT$                      TABLE                                                  SYSTEM                                11.00
SYS                            OBJ$                           TABLE                                                  SYSTEM                                10.00

20 rows selected.

SQL> 

You may see many of the larger segments being LOB segments. You can get more information about LOB segments specifically using the following top-n query.

SQL> 
SQL> show user;
USER is "SYS"
SQL> 
SQL> SET LINESIZE 200
SQL> COLUMN owner FORMAT A30
SQL> COLUMN table_name FORMAT A30
SQL> COLUMN column_name FORMAT A30
SQL> COLUMN segment_name FORMAT A30
SQL> COLUMN tablespace_name FORMAT A30
SQL> COLUMN size_mb FORMAT 99999999.00
SQL> 
SQL> SELECT *
  2  FROM   (SELECT l.owner,
  3                 l.table_name,
  4                 l.column_name,
  5                 l.segment_name,
  6                 l.tablespace_name,
  7                 ROUND(s.bytes/1024/1024,2) size_mb
  8          FROM   dba_lobs l
  9                 JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
 10          ORDER BY 6 DESC)
 11  WHERE  ROWNUM <= 20;

OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                   TABLESPACE_NAME                     SIZE_MB
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
SYS                            WRI$_EMX_FILES                 BINARY_DATA                    SYS_LOB0000007422C00005$$      SYSAUX                                88.19
MDSYS                          SDO_COORD_OP_PARAM_VALS_EXT    PARAM_VALUE_FILE               SYS_LOB0000072920C00006$$      SYSAUX                                72.13
MDSYS                          SDO_COORD_OP_PARAM_VALS        PARAM_VALUE_FILE               SYS_LOB0000064076C00006$$      SYSAUX                                56.19
SYS                            WRI$_EMX_FILES                 DATA                           SYS_LOB0000007422C00004$$      SYSAUX                                48.19
SYS                            AW$AWXML                       AWLOB                          SYS_LOB0000066421C00004$$      SYSAUX                                24.19
SYS                            WRH$_SQL_PLAN                  OTHER_XML                      SYS_LOB0000011147C00038$$      SYSAUX                                16.19
MDSYS                          SDO_XSD_TABLE                  XSD_DOC                        SYS_LOB0000067342C00002$$      SYSAUX                                16.19
SYS                            METASTYLESHEET                 STYLESHEET                     SYS_LOB0000001362C00003$$      SYSTEM                                13.00
SYS                            OPATCH_XINV_TAB                XML_INVENTORY                  SYS_LOB0000010461C00001$$      SYSAUX                                 7.19
SYS                            AW$AWMD                        AWLOB                          SYS_LOB0000066406C00004$$      SYSAUX                                 7.19
SYS                            WRH$_SQLTEXT                   SQL_TEXT                       SYS_LOB0000011139C00004$$      SYSAUX                                 4.19
SYS                            REGISTRY$SQLPATCH_RU_INFO      PATCH_DIRECTORY                SYS_LOB0000022516C00008$$      SYSTEM                                 4.00
SYS                            AW$EXPRESS                     AWLOB                          SYS_LOB0000066401C00004$$      SYSAUX                                 3.19
SYS                            AW$AWCREATE                    AWLOB                          SYS_LOB0000066411C00004$$      SYSAUX                                 3.19
SYS                            KOTTD$                         SYS_NC_ROWINFO$                SYS_LOB0000000768C00002$$      SYSTEM                                 3.00
SYS                            AW$AWCREATE10G                 AWLOB                          SYS_LOB0000066416C00004$$      SYSAUX                                 2.19
SYS                            AW$AWREPORT                    AWLOB                          SYS_LOB0000066426C00004$$      SYSAUX                                 2.19
SYS                            WRI$_ADV_SQLT_PLANS            OTHER_XML                      SYS_LOB0000008766C00040$$      SYSAUX                                 1.19
MDSYS                          SDO_XDB_RESOURCES              DATA                           SYS_LOB0000072925C00002$$      SYSAUX                                 1.19
SYS                            WRI$_DBU_FEATURE_USAGE         FEATURE_INFO                   SYS_LOB0000009065C00008$$      SYSAUX                                 1.19

20 rows selected.

SQL> 

Row Movement

The ALTER TABLE ... SHRINK SPACE command moves rows between existing blocks to compact the data, so before you attempt to shrink a table segment you need to enable row movement. You can check if row movement is already enabled by querying the ROW_MOVEMENT column of the [DBA|ALL|USER]_TABLES views.

SQL> 
SQL> conn scott/tiger@PDB1;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> SELECT row_movement
  2  FROM   user_tables
  3  WHERE  table_name = 'EMP';

ROW_MOVEMENT
------------------------
ENABLED

SQL> ALTER TABLE EMP DISABLE ROW MOVEMENT;

Table altered.

SQL> SELECT row_movement
  2  FROM   user_tables
  3  WHERE  table_name = 'EMP';

ROW_MOVEMENT
------------------------
DISABLED

SQL> 

Row movement is enabled with the following command.

SQL> 
SQL> show user;
USER is "SCOTT"
SQL> ALTER TABLE EMP ENABLE ROW MOVEMENT;

Table altered.

SQL> SELECT row_movement
  2  FROM   user_tables
  3  WHERE  table_name = 'EMP';

ROW_MOVEMENT
------------------------
ENABLED

SQL> 

SecureFile LOBs

When using basicfile LOBs the shrink commands work as expected. To demonstrate this we need to create the following table containing a basicfile LOB column.

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 14 11:46:04 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL> 
SQL> DROP TABLE lob_tab PURGE;
DROP TABLE lob_tab PURGE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> CREATE TABLE lob_tab (
  2    id NUMBER,
  3    data CLOB
  4  )
  5  LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW);

Table created.

SQL> INSERT INTO lob_tab VALUES (1, 'ONE');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 

We can see both shrink commands complete without errors.

SQL> 
SQL> 
SQL> ALTER TABLE lob_tab MODIFY LOB(data)(SHRINK SPACE);

Table altered.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL> SHOW USER;
USER is "MAXWELLPAN"
SQL> 

Now recreate the table using a securefile LOB column.

SQL> 
SQL> show user;
USER is "MAXWELLPAN"
SQL> 
SQL> DROP TABLE lob_tab PURGE;

Table dropped.

SQL> CREATE TABLE lob_tab (
  2    id NUMBER,
  3    data CLOB
  4  )
  5  LOB(data) STORE AS SECUREFILE (DISABLE STORAGE IN ROW);

Table created.

SQL> INSERT INTO lob_tab VALUES (1, 'ONE');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> 

Now the first command fails, but adding the CASCADE option appears to make it work.

SQL> 
SQL> show user;
USER is "MAXWELLPAN"
SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);
ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL> 

Unfortunately, the second command doesn't work and the securefile LOB segment is not shrunk.

Instead, to shrink a securefile LOB segment you need to move it. In the following example the move is to the same tablespace.

SQL> ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);

Table altered.

SQL> 

Comments and Restrictions

Here are some things to consider before performing shrink operations.

• Moving rows can cause problem with rowid based triggers.
• Rowid materialized views must be rebuilt after a shrink operation.
• The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.
• You can't combine the SHRINK SPACE clause with any other ALTER TABLE clauses.
• You can't shrink a cluster or a clustered table.
• You can't shrink any object with a LONG column.
• You can't shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
• You can't shrink tables that are the master table of an ON COMMIT materialized view
• Mapping tables of index-organized tables are not affected by a shrink.
• Shrinks can't be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).
• The shrink operation against a table doesn't cascade to the LOB segments. They need to handled separately.
• You can't shrink securefile LOB segments.
• Changing the arrangement of rows in a table can have a negative impact on performance in some circumstances. Test thoroughly before making any decisions.
• After any structural change, like a move, remember to check for unusuable indexes. You can use the unusuable_indexes.sql script to find them. If you have any, rebuild them.