Re: how to get a list of schemas on an oracle9i database

From: Hans Forbrich <news.hans_at_telus.net>
Date: Mon, 27 Sep 2004 19:51:05 GMT
Message-ID: <Ja_5d.122049$KU5.107413@edtnps89>

Alan wrote:

> is there a sql command that will give me a list of schemas in an
> oracle9i database; I've looked through the other postings that ask
> this and I'm just not seeing the results I need (e.g. select username
> from dba_users, etc).
>
> Is there an answer or have I got this all wrong?
>
> Thanks

What are you attempting to do? WHat in particular do you need?

In the simplest analysis, a schema is simply a user (from dba_users) that has ability to define objects (has the generic 'resource' role, or at least one of the 'create' object privileges.)

A slightly more complex interpretation is a schema is a user that actually owns some database objects as found in dba_objects ... a lot of the DBA_* views have an OWNER column, and a select distinct thereof on dba_objects should help out.

IMO, the basic difference between a schema and a user is that a schema has object/resource ownership-related privileges whereas a user has session-related privileges.

By far the easiest, however, is to use the Oracle Enterprise Manager 9i console and expand the SCHEMA tab.

HTH
/Hans Received on Mon Sep 27 2004 - 14:51:05 CDT