This discussion is archived
5 Replies Latest reply: Jan 2, 2013 4:47 AM by hitgon RSS

Query

936749 Newbie
Currently Being Moderated
Hi,
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"
from dba_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.
Regards
  • 1. Re: Query
    936749 Newbie
    Currently Being Moderated
    select OWNER,count(object_name) as "Num Objects",OBJECT_TYPE
    from dba_objects
    group by OWNER, OBJECT_TYPE

    works but I'd like total number objects and then number of different objects on the same row.
    Regards
  • 2. Re: Query
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    select owner, object_type, count(*) from dba_objects group by owner, object_type order by owner;
    for different Object you could use Minus or tools such as toad and SQL developer
  • 3. Re: Query
    hitgon Expert
    Currently Being Moderated
    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
  • 4. Re: Query
    936749 Newbie
    Currently Being Moderated
    hitgon wrote:
    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
    Hi,
    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?
    Cheers
  • 5. Re: Query
    hitgon Expert
    Currently Being Moderated
    Select owner, count(*) from dba_objects
    where owner NOT IN ('SYS','SYSTEM')
    group by owner order by owner;

    Edited by: hitgon on Jan 2, 2013 6:17 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points