Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

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