getting the back end server name from an oracle db

In our hosting environment we have many servers running may different oracle instances.

Execute this query to get the back end server name (could still be a virtual server of course):

select HOST_NAME from v$instance

The only problem with this is that the user executing the SQL must have select permission on “sys.v$instance”. A better solution is to use the in-built SYS_CONTEXT package, which is available to everyone and requires no extra permissions to run.

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

More information is available here

Advertisement