Oracle PL/SQL/System Tables Views/all users
Join all_objects and all_users to list user and its object count
SQL>
SQL>
SQL> select a.username, (select count(*)
2 from all_objects b
3 where b.owner = a.username) cnt
4 from all_users a
5 /
USERNAME CNT
------------------------------ ----------
SYS 6520
SYSTEM 422
OUTLN 7
DIP 0
TSMSYS 2
INV15 2
DBSNMP 46
INV10 2
CTXSYS 338
XDB 334
ANONYMOUS 0
USERNAME CNT
------------------------------ ----------
MDSYS 458
HR 34
FLOWS_FILES 11
FLOWS_020100 1085
sqle 530
INV11 2
INV12 2
INV13 2
INV14 2
PLSQL 0
INV16 2
USERNAME CNT
------------------------------ ----------
INV17 2
INV18 2
INV19 2
INV20 2
DEFINER 4
27 rows selected.
SQL>
User and its average object id
SQL>
SQL> select a.username, (select count(*) from all_objects b where b.owner = a.username) cnt,
2 (select avg(object_id ) from all_objects b where b.owner = a.username) avg
3 from all_users a
4 /
USERNAME CNT AVG
------------------------------ ---------- ----------
SYS 6520 5009.74064
SYSTEM 422 6095.87678
OUTLN 7 1172.57143
DIP 0
TSMSYS 2 8606.5
INV15 2 16237.5
DBSNMP 46 9592.65217
INV10 2 16227.5
CTXSYS 338 9877.92012
XDB 334 10800.7485
ANONYMOUS 0
USERNAME CNT AVG
------------------------------ ---------- ----------
MDSYS 458 11667.2009
HR 34 12104.5
FLOWS_FILES 11 12717.2727
FLOWS_020100 1085 12813.424
sqle 530 16254.6849
INV11 2 16229.5
INV12 2 16231.5
INV13 2 16233.5
INV14 2 16235.5
PLSQL 0
INV16 2 16239.5
USERNAME CNT AVG
------------------------------ ---------- ----------
INV17 2 16241.5
INV18 2 16243.5
INV19 2 16245.5
INV20 2 16247.5
DEFINER 4 16250.5
27 rows selected.
SQL>
SQL>