SELECT USERNAME,
COUNT(DECODE(o.TYPE#, 2,o.OBJ#,'')) "TableS",
COUNT(DECODE(o.TYPE#, 1,o.OBJ#,'')) "Indices",
COUNT(DECODE(o.TYPE#, 5,o.OBJ#,'')) "Synonyms",
COUNT(DECODE(o.TYPE#, 4,o.OBJ#,'')) "Views",
COUNT(DECODE(o.TYPE#, 6,o.OBJ#,'')) "Sequences",
COUNT(DECODE(o.TYPE#, 7,o.OBJ#,'')) "Procedures",
COUNT(DECODE(o.TYPE#, 8,o.OBJ#,'')) "Functions",
COUNT(DECODE(o.TYPE#, 9,o.OBJ#,'')) "Packages",
COUNT(DECODE(o.TYPE#,12,o.OBJ#,'')) "Triggers",
COUNT(DECODE(o.TYPE#,10,o.OBJ#,'')) "Dependencies"
FROM obj$ o, dba_users u
WHERE u.USER_ID = o.OWNER# (+)
GROUP BY USERNAME
ORDER BY USERNAME
Saturday, January 23, 2010
Get numbers of objects belong to each user
at 6:38 AM
Labels: Oracle, Oracle Query, PL/SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment