I'd like to create a report with distinct schemas with objects and number of different objects.
This is have far I've come:
select OWNER,count(object_name) as "Num Objects"
where owner not like ('%SYS%')
group by OWNER;
So what I need is to get
SCHEMA - Total Number of objects - Number of tables, Number of synonyms, Number of indexes and so on.
Can someone help me on the way please.
select OWNER,count(object_name) as "Num Objects",OBJECT_TYPE
group by OWNER, OBJECT_TYPE
works but I'd like total number objects and then number of different objects on the same row.
SET PAGESIZE 50000
select owner, object_type, count(*) from dba_objects
where owner NOT IN ('SYS','SYSTEM')
group by owner, object_type order by owner;
Edited by: hitgon on Jan 2, 2013 6:06 PM
This works and is almost the same as I postet above. Do anyone know how I could get it all on one row for each schema?