Obtaining a list of all databases via SQL

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.

nope

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)

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

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'

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

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?

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

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?

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

but really the question - how can i tell what databases are on my server, only arises when it isn't properly managed. Or you have taken over management and not got any docs or anyone who knows

That is absolutely true.

I know this is not sql but in unix, you could do a
ps -ef |grep oracle - this will show all the oracle database processes running
or even still
ps -ef |grep smon

and 2 of my databases are shut down - but they are still there?

Or I could even have an instance but no database :rolleyes:

Can we all agree that if we were required to use SQLPLUS ONLY to determine the databases on a system, we'd ask for our coat and hat.

(and recommend they use sql server?)

Maybe we should just let this thread unravel. It was good, stimulating thought.

deal :)