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

[eugene_k]

Can somebody point me to the particular view?
Thanks in advance.

[sambavan]

[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

[eugene_k]

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?

[xsf3190]

Will "SELECT UserName FROM DBA_USERS;" give you what you want ?
Mark.

[eugene_k]

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

[aarroyob]

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

[xsf3190]

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)