I fdund this query online.
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb'
"Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round ( NULLIF (blocks, 0) * 8, 2)
)*100 -10 "reclaimable space % " from dba_tables where OWNER ='DATA_INTEGRATION'
Below are the results.
OWNER TABLE_NAME Fragmented size Actual size ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_RO reclaimable space %
------------------------------ ------------------------------ ------------------------------------------ ------------------------------------------ ------------------------------------------ --------------------
DATA_INTEGRATION DI_APRIMO_LABEL_CONFIG 72kb 69.73kb 2.27kb -6.8472222
DATA_INTEGRATION DI_CONTACT_DELTA_EMAIL 32kb 10.55kb 21.45kb 57.03125
DATA_INTEGRATION DI_APRIMO_ACTIVITIES 144kb 190.32kb -46.32kb -42.166667
DATA_INTEGRATION DI_ACTIVITY_INTERACTION 288kb 393.5kb -105.5kb -46.631944
DATA_INTEGRATION DI_APRIMO_ETL_STAGING 40kb 0kb 40kb 90
DATA_INTEGRATION DI_APRIMO_EMAIL_SEND_STAGING 0kb 0kb 0kb
DATA_INTEGRATION DI_CONTACT_DELTA_CHANGES 5000kb 65.21kb 4934.79kb 88.6958
DATA_INTEGRATION DI_CAMPAIGN_TARGETS_DELTA 1438432kb 4.17kb 1438427.83kb 89.9997101
8 rows selected.
Can somebody explain what would cause the negative numbers for some of the tables. In addition, can the query be modified to find reclaiimable space > N% )ie 25%)