Database Administration (MOSC)

MOSC Banner

Free blocks in a table / segment

edited May 25, 2011 2:58AM in Database Administration (MOSC) 2 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center