Generate Shrink Table Statements (shrink_table.gen)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This generator script creates a complete shrink operation sequence: enable row movement, shrink the table with CASCADE option (includes indexes), then disable row movement. Only works with ASSM tablespaces.

The Script

rem  shrink_table.gen
rem
select 'alter table ' || owner || '.' || table_name || ' enable row movement;' || chr(10) ||
       'alter table ' || owner || '.' || table_name || ' shrink space cascade;' || chr(10) ||
       'alter table ' || owner || '.' || table_name || ' disable row movement;'
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table_name')
   and temporary = 'N'
   and iot_type is null
   and tablespace_name in (select tablespace_name
                           from dba_tablespaces
                           where segment_space_management = 'AUTO')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, table_name
/

Usage

-- Basic usage
@shrink_table.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)

Parameters

The script prompts for:

• &owner - Schema owner pattern (use % for wildcard)
• &table_name - Table name pattern (use % for wildcard)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES and DBA_TABLESPACES
-- Plus ALTER TABLE privilege

Sample Output

alter table HR.EMPLOYEES enable row movement;
alter table HR.EMPLOYEES shrink space cascade;
alter table HR.EMPLOYEES disable row movement;

alter table SCOTT.EMP enable row movement;
alter table SCOTT.EMP shrink space cascade;
alter table SCOTT.EMP disable row movement;

Key Features

• Complete Operation: Enables/disables row movement automatically
• CASCADE Option: Shrinks dependent segments (indexes, LOBs)
• ASSM Only: Only generates for Automatic Segment Space Management tablespaces
• Safety Checks: Excludes IOT and temporary tables

Common Use Cases

Shrink All Tables in Schema

@shrink_table.gen
-- Enter: HR for owner, % for table_name

Shrink Large Tables After Deletes

@shrink_table.gen
-- Enter: % for owner, %_ARCHIVE for table_name

Related Scripts

• Move Table Generator - Alternative space reclamation
• Maximum Shrink Analysis - Analyze shrink potential