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;

=======================

Shrink SubPartition

=======================

ALTER TABLE MY_USER.MY_TABLE ENABLE ROW MOVEMENT;

ALTER TABLE MY_USER.MY_TABLE MODIFY SUBPARTITION SYS_SUBP315772  SHRINK SPACE;

ALTER TABLE MY_USER.MY_TABLE MODIFY SUBPARTITION SYS_SUBP315772  DEALLOCATE UNUSED; 

Generate the sql above

SELECT 'ALTER TABLE '||USER_TAB_PARTITIONS.table_name||' MODIFY SUBPARTITION '||USER_TAB_SUBPARTITIONS.subpartition_name||' SHRINK SPACE;'||CHR(13)

       'ALTER TABLE '||USER_TAB_PARTITIONS.table_name||' MODIFY SUBPARTITION '||USER_TAB_SUBPARTITIONS.subpartition_name||'  DEALLOCATE UNUSED;' as sql_cmd

   FROM (

SELECT USER_TAB_PARTITIONS.table_name, 

       USER_TAB_SUBPARTITIONS.subpartition_name, 

   USER_TAB_SUBPARTITIONS.last_analyzed,       ROUND(USER_TAB_SUBPARTITIONS.blocks*DBA_TABLESPACES.block_size/1024/1024) as sub_part_used_mb,

       USER_TAB_SUBPARTITIONS.num_rows sub_part_num_rows

  FROM USER_TAB_PARTITIONS,

       USER_TAB_SUBPARTITIONS,

   DBA_TABLESPACES

 WHERE USER_TAB_SUBPARTITIONS.table_name = USER_TAB_PARTITIONS.table_name

   AND USER_TAB_SUBPARTITIONS.partition_name = USER_TAB_PARTITIONS.partition_name

   AND USER_TAB_SUBPARTITIONS.last_analyzed < TRUNC(SYSDATE)-2

   AND USER_TAB_PARTITIONS.partition_name <> 'BASE_PART'

   AND DBA_TABLESPACES.tablespace_name = 'IGT_TABLE_BIG'

   AND ROUND(USER_TAB_SUBPARTITIONS.blocks*DBA_TABLESPACES.block_size/1024/1024) > 10

   AND USER_TAB_PARTITIONS.table_name = 'SGA_W_PSMS_SUBSCRIBER'

   );

=======================

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_OWNER.MY_TABLE MODIFY PARTITION P_01_06 SHRINK SPACE;

ALTER TABLE MY_OWNER.MY_TABLE MOVE PARTITION P_01_06 TABLESPACE NEW_TBS_01;

ALTER TABLE MY_OWNER.MY_TABLE MOVE SUBPARTITION P_01_06 TABLESPACE NEW_TBS_01;

To Generate the SQL:

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

FROM USER_TAB_PARTITIONS WHERE table_name = 'MY_TABLE';