Re: Query system tables for a list of schemas in DB?

dominic.morris_at_eidosnet.co.uk (dom) wrote in message news:<c5c9bfea.0303030035.3265d867_at_posting.google.com>...
> Hi all,
>
> I'm trying to get a list of schemas from my Oracle 9i database, and
> the best thing I've found so far is; "select * from dba_allusers" --
> this is kind of Ok, but it yields a longer list than Oracle Enterprise
> Manager Console reports when I click on the Schema node of the
> treeview on the left. Ideally, I'd like them to be the same... anybody
> know how I can get this list?
>
> On a related note, I'm trying to enable full logging of Oracle
> Enterprise Manager's SQL in order that I can see what SQL it uses to
> get lists tables, keys, procedures, etc. I've looked at the
> Configuration menu's SQL Logging option, but that only seems to log
> action SQL, as opposed to SELECTs. If anybody can let me know how this
> is done, that would be much appreciated.
>
> And finally, I'm using a statement like "select * from all_tables
> where owner like '...'" in order to get a list of tables in a given
> schema; is there any way of knowing which of these tables are "system"
> tables? Is this concept valid in Oracle? I'm an Oracle newbie, so
> please excuse my ignorance!
>
> Many thanks in advance,
> dm.

Dom, if you only want a list of users who own objects try:

select distinct(owner) from dba_objects;

for counts of object types by owner:
select owner, object_type, count(*)
from dba_objects
group by owner, object_type

For just the list of users see dba_users

You will find an article on locating object information in the Oracle rdbms dictionary at http://www.jlcomp.demon.co.uk/faq/object_info.html

HTH -- Mark D Powell -- Received on Mon Mar 03 2003 - 19:23:10 CET