ttitle 'Object Count By User'

column U heading 'Username' format a10;

column TA heading 'Table' format 99999;

column IX heading 'Index' format 99999;

column VW heading 'View' format 99999;

column TR heading 'Trig' format 99999;

column PK heading 'Pkg' format 99999;

column PR heading 'Proc' format 99999;

column FN heading 'Func' format 99999;

column SY heading 'Syn' format 99999;

column SQ heading 'Seq' format 99999;

column DE heading 'Depnd' format 99999;

break on report

compute sum label "    Grand Totals:" of TA on report

compute sum label "    Grand Totals:" of IX on report

compute sum label "    Grand Totals:" of SY on report

compute sum label "    Grand Totals:" of VW on report

compute sum label "    Grand Totals:" of SQ on report

compute sum label "    Grand Totals:" of PR on report

compute sum label "    Grand Totals:" of FN on report

compute sum label "    Grand Totals:" of PK on report

compute sum label "    Grand Totals:" of TR on report

set maxdata 32000

SELECT

  username,

  COUNT( DECODE(O.type#, 2, o.obj#, '') ) TA,

  COUNT( DECODE(O.type#, 1, o.obj#, '') ) IX,

  COUNT( DECODE(O.type#, 5, o.obj#, '') ) SY,

  COUNT( DECODE(O.type#, 4, o.obj#, '') ) VW,

  COUNT( DECODE(O.type#, 6, o.obj#, '') ) SQ,

  COUNT( DECODE(O.type#, 7, o.obj#, '') ) PR,

  COUNT( DECODE(O.type#, 8, o.obj#, '') ) FN,

  COUNT( DECODE(O.type#, 9, o.obj#, '') ) PK,

  COUNT( DECODE(O.type#,12, o.obj#, '') ) TR

FROM

  sys.obj$ O,

  sys.dba_users U

WHERE

  U.user_id = O.owner# (+)

GROUP BY

  username

/

tti off

clear breaks

clear computes