how can I get a list of all databases that exist in an Oracle database installation by using normal SQL commands?

Bye and thanks,
Werner.

10-26-2004, 10:38 AM

davey23uk

nope

10-26-2004, 02:43 PM

hacketta1

On Unix I guess you could write a PL/SQL pipelined function that uses UTL_FILE to parse /etc/oratab and then query it using "normal" SQL (which doesn't mean you can't do something similar on Windows. I just don't know it very well)

10-26-2004, 02:54 PM

davey23uk

doesnt mean the database is there, just means there is an oratab entry

10-26-2004, 04:33 PM

KenEwald

I agree, there's no (sysdatabases - SQL Server) packaged view or procedure to query a list of databases, just convoluted workarounds.

but simpley there is no way to be sure what databases are on a server. None

10-27-2004, 05:52 AM

KenEwald

You must want to be proven wrong. I'd bet there's a big enough brain in the group to figure this one out. Is stecal still around?

Let's start here:

The original question was for a SQL query for all SID's under a given installation.

I know it's not SQL now, but how about:
ls -lrt $ORACLE_HOME/dbs/init*.ora $ORACLE_HOME/dbs/spfile*

Just thinking here ... about the rules.
Is $ORACLE_HOME set?
Is $ORACLE_HOME/bin in $PATH?
Is $ORACLE_SID set?
Is the $ORACLE_SID open and accepting connections?

10-27-2004, 06:28 AM

davey23uk

I won't be proven wrong on this one :-)

There may be an spfile or an init.ora file - does that mean there is a database there - no. Or I dont use oracle_home/dbs for my files

Is ORACLE_HOME set - yes, does that mean there is a database - no

Is the path set - yes, does that mean there is a database - no

Can I connect to a database - yes - does that tell me how many databases are on the server - no

10-27-2004, 08:07 AM

DaPi

I think I'm with Davey on this. There are lots of indications of what db's MIGHT be around, but it needs more that that to say you have a db.

It gets rather philosophical, is a corrupt db a db? You will have to define some kind of threshold to determine if a db exists. Perhaps a db is something that you can "startup mount", even if you can't open it?

10-27-2004, 08:33 AM

marist89

This is one of those yes and no type questions. Generically, no, there is no way you can tell how many databases reside on a host.

However, if you are in an environment where standards have been enforced, then you could use the oratab file. In my environment, I know that unless there is an entry in the oratab file, the database doesn't exist. I rely on the oratab file for monitoring and make sure it accurately reflects the contents of that server. (Actually, I liked KenEwald's idea of an external table.)