Doc

     Name:   objcount.sql

     Author: Mark Gurry

     This scripts provides you with a listing of all
     object types by user.

#

ttitle 'Object Count Byr User'

SELECT  username,
        COUNT(DECODE(o.type, 2, o.obj#, ‘’)) Tab,
        COUNT(DECODE(o.type, 1, o.obj#, ‘’)) Ind,
        COUNT(DECODE(o.type, 5, o.obj#, ‘’)) Syn,
        COUNT(DECODE(o.type, 4, o.obj#, ‘’)) Vew,
        COUNT(DECODE(o.type, 6, o.obj#, ‘’)) Seq,
        COUNT(DECODE(o.type, 7, o.obj#, ‘’)) Prc,
        COUNT(DECODE(o.type, 8, o.obj#, ‘’)) Fun,
        COUNT(DECODE(o.type, 9, o.obj#, ‘’)) Pck,
        COUNT(DECODE(o.type,12, o.obj#, ‘’)) Trg,
        COUNT(DECODE(o.type,10, o.obj#, ‘’)) Dep
  FROM  sys.obj$ o,  sys.dba_users U
 WHERE  u.user_id = o.owner# (+)
 GROUP  BY username;