Good Candidates to Move
To reclaim more space, we can move some tables 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, 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 table. I call it block-row ratio.
Top Emptiness Table
Let's see top 10 emptiness tables in our database before reorganization.
SQL> column table_name format a25;
SQL> column br_ratio format 9999.99;
SQL> select * from (select table_name, blocks/num_rows br_ratio from all_tables where owner ='ERP_APP' and partitioned = 'NO' and num_rows is not null order by 2 desc nulls last) where rownum <= 10;
TABLE_NAME BR_RATIO
------------------------- --------
PAYMENT_INV_LOG 567.77
PAYMENT_FOR_TRAN 476.37
PAYMENT_INV_BILL 8.00
...
10 rows selected.
As you can see, the first 2 tables have values 567.77 and 476.37. Which means, one row averagely takes 567.77 and 476.37 blocks respectively. They're good candidates to move.
After Reorganization
Let's check the ratio after defragmenting the tables.
SQL> select table_name, blocks/num_rows br_ratio from all_tables where owner ='ERP_APP' and table_name in ('PAYMENT_INV_LOG', 'PAYMENT_FOR_TRAN');
TABLE_NAME BR_RATIO
------------------------- --------
PAYMENT_INV_LOG .50
PAYMENT_FOR_TRAN 2.00
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 table partitions to shrink.