/*
 
**********************************************************
********* Needs to be run on ASM instance ****************
**********************************************************

Max and min columns should be close to each other for balanced files.

file# max min
=== === ===
3013   57  941
3018   58  942
3021   59  942
3043   940 942
3053   938 941

** All the disks in this example are having the same total capacity.

From the output above, files with imbalanced extents are file# 3013, 3018 and 3021. For example, for file# 3013 there are 59 extents in one disk and 942 extents in another disk.  


File# 3043 and 3053 have extents almost equally balanced. Small imbalances are expected.

*/

DEFINE diskgroup_number="&&1"

set pagesize 55
set linesize 90

SELECT group_kffxp Group#
     , number_kffxp file#
     , MAX(count1) MAX
     , MIN(count1) MIN
FROM
    (SELECT group_kffxp
         ,  number_kffxp
         ,  disk_kffxp
         ,  COUNT(XNUM_KFFXP) count1
    FROM  sys.x$kffxp
    WHERE group_kffxp = &diskgroup_number
    ANd disk_kffxp != 65534
    GROUP BY group_kffxp, number_kffxp, disk_kffxp
    )
GROUP BY group_kffxp, number_kffxp;
ORDER BY group_kffxp
       , number_kffxp

UNDEFINE diskgroup_number