Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql command to list all databases in the oracle server

Re: sql command to list all databases in the oracle server

From: John K. Hinsdale <hin_at_alma.com>
Date: 29 Dec 2006 03:32:20 -0800
Message-ID: <1167391940.393298.275100@73g2000cwn.googlegroups.com>


dalu.gelu_at_gmail.com wrote:

> could u help me, what is the sql command to list all existing
> databases in a oracle server?

Depends what you mean by "database" and "server". If "server" means a physical, computer server and you want to see all the Oracle instances ("databases"), on most Unixes you can run

      /bin/ps -ef | grep ora_pmon | grep -v grep

and you will see output like:

     oracle  2345     1  0   Dec 22 ?        0:00 ora_pmon_APPLE
     oracle  2844     1  0   Dec 22 ?        0:00 ora_pmon_ORANGE
     oracle  2787     1  0   Dec 22 ?        0:00 ora_pmon_PEAR

This would indicate you have three distinct instances, APPLE, ORANGE, and PEAR on that *machine*. Note that this is only the instances running right now, and that the machine could have installed and configured additional instances that are not running for some reason (e.g., they are disabled in the "oratab" startup).

But if by "server" you mean an Oracle instance, and by database you mean the separate schemas in that instance, you can connect to an instance and run:

    select owner from dba_tables
    union
    select owner from dba_views;

Note you have to combine owners of tables and views, as some schemas may exist solely for the purpose of owning views (I have such ones where I work).

Above is only schemas/users who actually have existing tables/views. For an more comprehensive list, you can run

    select username from dba_users;

which will give all the existing users/schemas that could potentially own database objects (tables, views, sequences, triggers, stored procedures) and be given various privileges (CREATE TABLE, etc.). Note that this list includes Oracle's "internal" users like SYS, SYSTEM, SYSMAN, OUTLN, DBSNMP, etc. which are not really "databases" from the Oracle users's point of view.

Hope that helps,

John Hinsdale Received on Fri Dec 29 2006 - 05:32:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US