Saturday, January 23, 2010

Get numbers of objects belong to each user

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

No comments:

Post a Comment

Oracle Query