[oracle] Find the server name for an Oracle database

Is there a way of finding the name of the server an Oracle database is hosted on?

I use this query in order to retrieve the server name of my Oracle database.

SELECT program FROM v$session WHERE program LIKE '%(PMON)%';

If you don't have access to the v$ views (as suggested by Quassnoi) there are two alternatives

select utl_inaddr.get_host_name from dual

and

select sys_context('USERENV','SERVER_HOST') from dual

Personally I'd tend towards the last as it doesn't require any grants/privileges which makes it easier from stored procedures.

The query below demonstrates use of the package and some of the information you can get.

select sys_context ( 'USERENV', 'DB_NAME' ) db_name,
sys_context ( 'USERENV', 'SESSION_USER' ) user_name,
sys_context ( 'USERENV', 'SERVER_HOST' ) db_host,
sys_context ( 'USERENV', 'HOST' ) user_host
from dual

NOTE: The parameter ‘SERVER_HOST’ is available in 10G only.

Any Oracle User that can connect to the database can run a query against “dual”. No special permissions are required and SYS_CONTEXT provides a greater range of application-specific information than “sys.v$instance”.