SEO

#1 (**permalink**) 02-26-2008, 07:50 AM

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.

#2 (**permalink**) 02-26-2008, 07:50 AM

On Jan 14, 9:54*am, midas <mcneeley1...@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

#3 (**permalink**) 02-26-2008, 07:50 AM

Thank you so much - this is exactly what we are looking for!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

List of existing schemas