5 Replies Latest reply: Jan 2, 2013 6:47 AM by hitgon RSS

    Query

    936749
      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
          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
            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
              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
                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
                  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