Database Tuning (MOSC)

MOSC Banner

How to asses table fragmentation

edited Apr 21, 2019 4:34PM in Database Tuning (MOSC) 6 commentsAnswered

Will someone please help me understand this? Oracle database 12.2.0.1. I've seen the following query all over the Internet for identifying table fragmentation.

select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)  "wasted space kb",
((round((blocks*8),2)-  round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented" 
from dba_tables;

When I run this query for table SAPT, my understanding is that if I reorg this table, then column 4 'wasted space kb' is the amount of space I should expect to get back. Although in the example below that is not the case. FYI, I gathered stats before and after the reorg.

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