Fragmentation/ Reclaiming Wasted Space by Table Shrinking

select owner,table_name,round(((blocks*8)/1024/1024),2) "Size(GB)" ,round(((num_rows*avg_row_len/1024))/1024/1024,2) "Actual_Data(GB)",round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2) "Wasted_Space(GB)", round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) "Reclaimable Space(%)", partitioned from dba_tables where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))

and round(((((blocks*8)-(num_rows*avg_row_len/1024))/(blocks*8))*100 -10),2) >20 -- More than  20% fragmentation

and round((((blocks*8)) - ((num_rows*avg_row_len/1024)))/1024/1024,2)>5 --Wasted Space is more than 5GB

order by 5 desc;

OWNER TABLE_NAME Size(GB) Actual_Data(GB) Wasted_Space(GB) Reclaimable Space(%) PARTITIONED
XLA XLA_DISTRIBUTION_LINKS 375.6 246.22 129.37 24.44 YES
ZX ZX_LINES_DET_FACTORS 162.61 108.24 54.36 23.43 NO
AR RA_CUST_TRX_LINE_GL_DIST_ALL 122.6 74.72 47.88 29.05 NO
ASO ASO_ORDER_FEEDBACK_T 54.91 8.16 46.75 75.14 NO
XLA XLA_AE_LINES 82.74 55.29 27.45 23.17 YES
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H 27 1.92 25.07 82.87 NO
AR RA_CUSTOMER_TRX_LINES_ALL 57.44 36.49 20.96 26.48 NO
ZX ZX_LINES 34.06 22.78 11.28 23.12 NO
AR AR_RECEIVABLE_APPLICATIONS_ALL 21.81 12.97 8.84 30.54 NO
XXCUST XXCUST_INTERFACE_LINES_ALL 15.39 8.9 6.49 32.16 NO
APPLSYS WF_JAVA_DEFERRED 5.08 0 5.08 90 NO

select owner,segment_name TABLE_NAME,segment_type,round(bytes/1024/1024/1024,2) "Size(GB)"

from dba_segments

where segment_type='TABLE'

and owner='APPLSYS'

and round(bytes/1024/1024/1024,2)>1

order by 4 desc;

OWNER TABLE_NAME SEGMENT_TYPE Size(GB)
APPLSYS FND_LOG_MESSAGES TABLE 86.08
APPLSYS WF_ITEM_ACTIVITY_STATUSES_H TABLE 27.11
APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE 20.38
APPLSYS FND_FLEX_VALUE_HIERARCHI_A TABLE 10
APPLSYS WF_ITEM_ACTIVITY_STATUSES TABLE 6.95
APPLSYS WF_NOTIFICATION_ATTRIBUTES TABLE 5.35
APPLSYS WF_JAVA_DEFERRED TABLE 5.1
APPLSYS WF_DEFERRED TABLE 4.8
APPLSYS FND_CONCURRENT_REQUESTS TABLE 3.51
APPLSYS FND_DOCUMENTS_TL TABLE 2.95
APPLSYS FND_LOGINS TABLE 2.16
APPLSYS FND_SEARCHABLE_CHANGE_LOG TABLE 1.97
APPLSYS WF_NOTIFICATIONS TABLE 1.79
APPLSYS DR$FND_LOBS_CTX$I TABLE 1.48

References

http://expertoracle.com/2017/05/07/reorg-tables-and-indexes-in-oracle-ebs-applications-best-practices/
http://select-star-from.blogspot.com/2013/09/how-to-check-table-fragmentation-in.html
How to Find and Remove Table Fragmentation in Oracle Database

https://www.oracle.com/technetwork/articles/database/fragmentation-table-shrink-tips-2330997.html

#tuning