How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement?

Is it possible to find databases in a cluster? i.e. databases present on the network on some other system?

Could I analyze the files present on an Oracle installation to find the same?

Given complete access credentials to an Oracle system how would you go about enumerating all the databases that exist?

Is there an equivalent to MySQL SHOW DATABASES statement?

There is no such thing. You can query listeners on a machine (lsnrctl status) to see what services are registered there, but that doesn't map one-to-one to database (and there could be multiple listeners on the same machine). Otherwise, the tools commonly used connect to one database instance, and an instance belongs to a single database.

If you're talking about Oracle RAC clusters, then each instance knows of its peers (other instances that service the same database) and you can find the other instances currently started for that database using the gv$instance view.

You can also use the crsctl utility to list the services (including databases) that are registered in the cluster, and their status.

If you're talking about another vendor's clustering software, I'm pretty sure they all have these types of resource management utilities to query.

If you're talking about just a bunch of machines, then no, there's no 100% reliable way of enumerating all databases on a network.

To find active (i.e. started) databases, look for _pmon_ processes on Unix (there's one per database instance), and Oracle services on Windows.

To locate installations of Oracle database software, look at /etc/oratab on Unix. This should contain all the ORACLE_HOMEs installed. You can look inside each of those in $ORACLE_HOME/dbs for spfile.ora and/or init.ora files - there will be one for each database.

(I believe you can find the equivalent of the information in oratab in the Windows registry keys below HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, but I don't know its structure.)

Now, of course, if you've registered all your database with an OEM (Enterprise Manager) server when you installed them, you can find the complete list there - but I guess if you're asking that's not the case.

StackExchange Database Administrators Q#27725, answer score: 29