Join all_objects and all_users to list user and its object count : all_users « System Tables Views « Oracle PL / SQL






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
JAVA2S                                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>

   
    
    
  








Related examples in the same category

1.User and its average object id