Re: List of existing schemas

From: <>
Date: Mon, 14 Jan 2008 08:28:56 -0800 (PST)
Message-ID: <>

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

Original text of this message