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'