Free blocks in a table / segment
Hi,
We were wondering if there is a way to identify the tables in a schema with empty blocks / fragmentation particularly the tables which undergoes lots of DELETES. The purpose is to identify all those tables and perform a re-org. The tablespace where the segment data is stored is ASSM / LMT and the database is 10.2.0.4
Lets use an example
SQL> EXEC DBMS_Stats.Gather_Table_Stats('APPS_MAIN', 'FAXES');
PL/SQL procedure successfully completed.
SQL> select (avg_row_len*num_rows)/1024/1024 from user_tables where table_name='FAXES';
(AVG_ROW_LEN*NUM_ROWS)/1024/1024
--------------------------------
70.922718
SQL> select sum(bytes)/1024/1024 from user_segments where segment_name='FAXES';
0