DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to extract all schema's names from the data dictionary?

  1. #1
    Join Date
    Nov 2001
    Posts
    3
    Can somebody point me to the particular view?
    Thanks in advance.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    [code]

    SELECT DISTINCT object_name,object_type, owner
    FROM dba_objects
    GROUP BY owner, object_type, object_name
    ORDER BY owner, object_type, object_name;

    [code]

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Nov 2001
    Posts
    3
    Thank you Sam,
    But I don't need all objects. I need only schema's names.
    Of couse I can do like this
    SELECT DISTINCT owner FROM dba_objects
    but it's seems to me it will take a lot of time if there are a lot of objects. I just curious is there any special view with only schema's names?

  4. #4
    Join Date
    Jan 2002
    Posts
    13
    Will "SELECT UserName FROM DBA_USERS;" give you what you want ?
    Mark.

  5. #5
    Join Date
    Nov 2001
    Posts
    3
    >xsf3190
    >Will "SELECT UserName FROM DBA_USERS;" give you what you want ?
    Not exactly. The fact is, there could be some users without any obejects that are owned by them. I just don't need those users.

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    I don't understand exactly what you want but maybe with this:

    select owner,count(1)
    from dba_objects
    group by owner;

    This will say you all the users in your database and how many objects they have. If you want only those which more than X objects:

    select owner,count(1)
    from dba_objects
    group by owner
    having count(1)>X;

    Hope that helps

    Angel

  7. #7
    Join Date
    Jan 2002
    Posts
    13
    To make it quicker than doing full scan on DBA_OBJECTS you could use:

    SELECT UserName FROM DBA_USERS
    where exists (select null from dba_segments where owner=username)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width