Shrink space Oracle

Shrink Table

ALTER TABLE MY_USER.MY_TABLE ENABLE ROW MOVEMENT;

ALTER TABLE MY_USER.MY_TABLE SHRINK SPACE;

ALTER TABLE MY_USER.MY_TABLE DEALLOCATE UNUSED;

Shrink Index

SELECT INDEX_NAME FROM DBA_INDEXES 

WHERE OWNER = 'MY_USER' AND table_name = 'MY_TABLE';

ALTER INDEX MY_USER.MY_INDEX REBUILD ONLINE;

Rebuild Partitioned Indexes

SELECT 'ALTER INDEX '||DBA_IND_PARTITIONS.index_owner||'.'||DBA_IND_PARTITIONS.index_name||' REBUILD PARTITION '||DBA_IND_PARTITIONS.partition_name||';'

  FROM DBA_IND_PARTITIONS       

WHERE DBA_IND_PARTITIONS.index_name = 'MY_INDEX';

  AND DBA_IND_PARTITIONS.index_owner = 'MY_OWNER';

  

ALTER INDEX DEU_O2Q7Q_SPARX.SFI_CUSTOMER_PROFILE_PK REBUILD PARTITION SYS_P49;

Generate for non-partitioned Objects

set linesize 120

set pagesize 0

set heading off

set feedback off

spool enable_row_movement.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||'  ENABLE ROW MOVEMENT;' FROM DBA_TABLES WHERE tablespace_name = 'IGT_TABLE';

spool off

spool shrink_space.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||'  SHRINK SPACE;' FROM DBA_TABLES WHERE tablespace_name = 'IGT_TABLE';

spool off

spool deallocate_unused.sql

SELECT 'ALTER TABLE '||owner||'.'||table_name||'  DEALLOCATE UNUSED;' FROM DBA_TABLES WHERE tablespace_name = 'IGT_TABLE';

spool off

ALTER TABLE MY_USER.MY_TABLE ENABLE ROW MOVEMENT;

ALTER TABLE MY_USER.MY_TABLE SHRINK SPACE;

ALTER TABLE MY_USER.MY_TABLE DEALLOCATE UNUSED;

spool rebuild_index_online.sql

SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD ONLINE;' FROM DBA_INDEXES WHERE tablespace_name = 'IGT_TABLE';

spool off

@enable_row_movement.sql

@shrink_space.sql

@deallocate_unused.sql

@rebuild_index_online.sql

For Partitioned Objects:

ALTER TABLE MY_TABLE MODIFY PARTITION P_01_06 SHRINK SPACE;

To Generate the SQL:

SELECT 'ALTER TABLE '||table_name||' MODIFY PARTITION '||partition_name||' SHRINK SPACE;' 

FROM USER_TAB_PARTITIONS WHERE table_name = 'MY_TABLE';