Find the server name for an Oracle database
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”.