=======================
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';
