Good Candidates to Move
To reclaim more space, we can move some table partitions to the same or different tablespace in order to make the segment more compact and release unused space. To find the best candidates to defragment, we use an indicator to select them.
Block-Row Ratio
The indicator is to measure the emptiness of a table partition, in which, we calculate how many blocked has been used by its all rows, the higher value of the ratio, the more empty of the partition. I call it block-row ratio.
Top Emptiness Table Partition
Let's see top 10 emptiness table partitions in our database before reorganization.
SQL> column table_name format a25;
SQL> column partition_name format a25;
SQL> column br_ratio format 9999.99;
SQL> select * from (select table_name, partition_name, blocks/num_rows br_ratio from all_tab_partitions where table_owner ='ERP_APP' and num_rows <> 0 and num_rows is not null order by 3 desc nulls last) where rownum <= 10;
TABLE_NAME PARTITION_NAME BR_RATIO
------------------------- ------------------------- --------
BILL_HIST FY_2023 1699.48
BILL_HIST FY_2022 341.33
BILL_HIST_LOG FY_2024 .44
BILL_HIST FY_2024 .05
...
As you can see, the first partition has the highest value 1699.48. Which means, one row averagely takes 1699.48 blocks. It's a good candidate to move.
After Reorganization
Let's check the ratio after moving the table partition.
SQL> select table_name, partition_name, blocks/num_rows br_ratio from all_tab_partitions where table_owner = 'ERP_APP' and table_name = 'BILL_HIST' and partition_name = 'FY_2023';
TABLE_NAME PARTITION_NAME BR_RATIO
------------------------- ------------------------- --------
BILL_HIST FY_2023 .04
If you saw nothing changed, please analyze the table, then check again.
In the similar way, we can use the same technique to find good candidates for non-partitioned table to shrink.