Re: List of existing schemas
Date: Mon, 14 Jan 2008 08:28:56 -0800 (PST)
Message-ID: <56a0bbf4-9fbc-4491-ba69-d26d9126fb1e@d4g2000prg.googlegroups.com>
On Jan 14, 9:54 am, midas <mcneeley1..._at_yahoo.com> wrote:
> We do not have experience in Oracle so if this question is posted in
> the wrong area, please respond with the best location for this post...
>
> We are looking for a query that we can run against each of our Oracle
> servers that will show each database name, instance name, and all the
> schemas. We are trying to build some documentation of our
> environment. So far we have been able to determine a list of users by
> doing a basic query of dba_users, however we would like to make sure
> that we have all databases/instances to drill down into for the user
> list. Any suggestions would be appreciated.
A user does not necessarily have a schema. A schema, though, always belongs to a user. To find all actual schemas I'd be looking at dba_objects:
select distinct owner
from dba_objects;
This result would be your schema owners, and it's likely a shorter list than that you obtained by querying dba_users.
The V$INSTANCE and V$DATABASE views can supply the instance name and database name for you. Presuming you're running at least 9.2.0.x:
break on db_name on instance_name skip 1 set pagesize 200
with get_schemas as(
select distinct owner
from dba_objects
)
select name db_name, instance_name, owner from v$instance, v$database, get_schemas;
This should return all of the information you asked about.
David Fitzjarrell Received on Mon Jan 14 2008 - 10:28:56 CST