Generate Move Table Statements (move_table.gen)

What This Script Does

This generator script creates ALTER TABLE MOVE statements to relocate tables to a different tablespace or reorganize them within the same tablespace. It excludes IOT and temporary tables.

The Script

rem  move_table.gen
rem
select 'alter table ' || owner || '.' || table_name || ' move' ||
       decode(upper('&new_tablespace'), '', '', ' tablespace ' || upper('&new_tablespace')) || ';'
  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 owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, table_name
/

Usage

-- Basic usage
@move_table.gen

-- When prompted, enter:
-- owner: Schema owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)
-- new_tablespace: Target tablespace (leave blank to reorganize in place)

Parameters

The script prompts for:

&owner - Schema owner pattern (use % for wildcard)
&table_name - Table name pattern (use % for wildcard)
&new_tablespace - Target tablespace name (optional)

Required Privileges

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

Sample Output

-- Move to new tablespace:
alter table HR.EMPLOYEES move tablespace USERS_NEW;
alter table HR.DEPARTMENTS move tablespace USERS_NEW;

-- Reorganize in place:
alter table SCOTT.EMP move;

Key Features

Flexible Tablespace: Optional new tablespace parameter
IOT Exclusion: Skips Index-Organized Tables
Temporary Table Exclusion: Skips temporary tables
System Schema Protection: Excludes critical system schemas

Common Use Cases

Move All Tables to New Tablespace

@move_table.gen
-- Enter: HR for owner, % for table_name, USERS_NEW for new_tablespace

Reorganize Tables to Reclaim Space

@move_table.gen
-- Enter: % for owner, %_ARCHIVE for table_name, leave new_tablespace blank

Rebuild Index Generator - Rebuild indexes after table move
Shrink Table Generator - Alternative space reclamation method