User and its average object id : all_users « System Tables Views « Oracle PL / 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
JAVA2S                                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>

   
    
    
    
  








Related examples in the same category

1.Join all_objects and all_users to list user and its object count