Obtaining a list of all databases via SQL
how can I get a list of all databases that exist in an Oracle database installation by using normal SQL commands?
Bye and thanks,
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?
Last edited by KenEwald; 10-27-2004 at 05:59 AM.
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.)
Click Here to Expand Forum to Full Width