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?

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.

SELECT  host_name
FROM    v$instance

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

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

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

Did you ask this question to use the same query in multiple environments and display wither this is Prod vs. UAT vs. Dev environments? Many companies have a standard suffix or prefix indicating the environment. Building on the answer above, here is a query that returns the environment, assuming last letter of 'p' - Prod | 'a' - UAT | 'd' - Dev.

select sys_context ( 'USERENV', 'DB_NAME' ) db_name,   /* Use this for determining Environment, where the last letter is p/a/d for Prod/uAt/Dev. */
       case substr ( sys_context ( 'USERENV', 'DB_NAME' ), -1 ) 
         when 'p' then 'PROD' 
         when 'u' then 'UAT' 
         when 'd' then 'Development' 
         else 'Unknown'
       end Environment
  from dual