Obtaining a list of all databases via SQL

[werner_schalk]

Hi guys,

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.

[davey23uk]

nope

[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)

[davey23uk]

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

[KenEwald]

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

Here's a few that haven't been mentioned.

1. External table (etc/oratab) - providing it's up-to-date. If you use DBCA to create/remove your db's, it'll have them.

2. A java stored procedure. I'm sure there's an class that'll shell/call the OS for "ps -ef|grep _smon|grep -v grep"

3. If you've got an 9.x OMS (Oracle Management Server) for OEM (Oracle Enterprise Manager) and have "discovered" your databases, you can query the following tables:

-- Discovered Servers --
select * from .smp_vdg_node_list

-- Discovered Databases #1 --
select * from .SMP_VDN_TARGET_LIST where typeid = 4

-- Discovered Databases #2 --
select value from .SMP_VDN_TARGET_PROPERTIES where name = 'oracle_sid'

4. If you've got 10g Grid Control and have the agent running on the servers of interest, and have the target.xml file properly configured, and cross your eyes just right:

-- MGMT_TARGETS --
select * from .MGMT_TARGETS where target_type = 'oracle_database'

[davey23uk]

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

[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?

[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

[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?

[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.)