Database Administration (MOSC)

MOSC Banner

Table Fragmentation size issue

edited Jan 24, 2022 6:28AM in Database Administration (MOSC) 14 commentsAnswered ✓

I am trying to find out table fragmentation and used below two queries( stats on this table collected very latest). I suppose dba_segment output size should be more than from dba_tables size output. But I am getting contradictory results. Please help someone explain what is wrong. Thanks in advance.


select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='ZOSEC';

240.375GB


select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"from all_tables WHERE table_name='ZOSEC';

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