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: Steve Howard <stevedhoward_at_gmail.com>
Date: 29 Dec 2006 08:41:26 -0800
Message-ID: <1167410486.341256.111230@79g2000cws.googlegroups.com>

John K. Hinsdale wrote:
> 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

Excellent, productive response. The only thing I would add is if the OP is on Windows (as it appears they have been using SQL Server), then check the services applet in the Control Panel for all registered instances.

Regards,

Steve Received on Fri Dec 29 2006 - 10:41:26 CST

Original text of this message

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