The count of segments in each user with or without stats

column schema_name format a19
column clusters_with format 99999999 heading "CLUSTERSWITHSTATS"
column tables_with format 99999999 heading "TABLESWITHSTATS"
column indexes_with format 99999999 heading "INDEXESWITHSTATS"
column clusters_without format 99999999 heading "CLUSTERSWITHOUTSTATS"c
olumn tables_without format 99999999 heading "TABLESWITHOUTSTATS"
column indexes_without format 99999999 heading "INDEXESWITHOUTSTATS"

select u.name schema_name, nvl(c.stats, 0) clusters_with, t.stats tables_with, nvl(i.stats, 0) indexes_with, nvl(c.total - c.stats, 0) clusters_without, t.total - t.stats tables_without, nvl(i.total - i.stats, 0) indexes_withoutfrom ( select o1.owner#, count(c1.avgchn) stats, count(*) total from sys.clu$ c1, sys.obj$ o1 where c1.obj# = o1.obj# group by o1.owner# ) c, ( select o2.owner#, count(t2.rowcnt) stats, count(*) total from sys.tab$ t2, sys.obj$ o2 where t2.obj# = o2.obj# and t2.tab# is null group by o2.owner# ) t, ( select o3.owner#, count(i3.blevel) stats, count(*) total from sys.ind$ i3, sys.obj$ o3 where i3.obj# = o3.obj# and i3.type# between 1 and 7 group by o3.owner# ) i, sys.user$ uwhere u.user# = t.owner# and u.user# = c.owner# (+) and u.user# = i.owner# (+)order by t.total - t.stats + nvl(c.total - c.stats, 0) + nvl(i.total - i.stats, 0)
/