Skip to Main Content
  • Questions

  • Determine machine name for application server from Oracle database

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, M K.

Asked: February 02, 2017 - 1:21 pm UTC

Last updated: November 05, 2019 - 4:11 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

A front end is hosted on an application server which connects to Oracle database.
How to determine the machine name of this application server from database.

From SYS_CONTEXT and v$session we do get client machine name (which is machine name of the user running performing some activity on the front end application) but this is not actual application machine name.

Let me know if I am not clear

and Connor said...

HOST will show the client (as considered from the perspective of the database)

If your client PC is truly connecting only to the app server, and the app server is connecting to the database, the sys_context('USERENV','HOST') should show the app server. For example, my asktom session talks to ORDS, which talks to the database - and I see this from my browser.

SQL> select sys_context('USERENV','HOST') from dual;

SYS_CONTEXT('USERENV','HOST')
--------------------------------------
ordshost


That is the ORDS host, not my laptop

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Truly connecting to app server

Mks, February 02, 2017 - 5:38 pm UTC

Thank you Connor

I think your statement "truly connected to app server" might make a difference. And I am not really able to interpret it.

Please find my understanding below, and let me know if my understanding is write

If we consider any banking or ticket booking applications. Here users logs into a application using browser and performs different operation on application by clicking on different menu options provided.

At this point, while user is performing operations , if we check v$session we can get os user name and the machine name . Both these are related to user, it is the username and the machine name of the machine that user is using.

So what you suggests is by using sys_context with host option we will get the machine name where app server is hosted or in other words the machine from where this application server scripts are kept.

Connor McDonald
February 03, 2017 - 7:37 pm UTC

"sys_context with host option we will get the machine name where app server is hosted"

Yes.

I'm very surprised that v$session is showing you a name from which the browser is running (unless an app is setting those values in some way).

Critical question

A reader, November 01, 2019 - 7:46 pm UTC

Dear sir

When Someone use my oracle username to update the database, according the updates recorded with my username.

Can we know the machine or IP name that use my oracle username or it is difficult?

All of us working with the same network (internal network)

Thank you
Connor McDonald
November 05, 2019 - 4:11 am UTC

If you enable auditing, you can capture some of that information. For example, I audited all SELECT and you'll see entries like this:

SQL> select * from dba_audit_trail where rownum = 1
  2  @pr
==============================
OS_USERNAME                   : GTX\hamcdc
USERNAME                      : MCDONAC
USERHOST                      : WORKGROUP\GTX
TERMINAL                      : GTX
TIMESTAMP                     : 18-APR-19
OWNER                         : SYS
OBJ_NAME                      : TAB$
ACTION                        : 3
ACTION_NAME                   : SELECT
...
...
EXTENDED_TIMESTAMP            : 18-APR-19 09.12.57.546000 AM +08:00
PROXY_SESSIONID               :
GLOBAL_UID                    :
INSTANCE_NUMBER               : 0
OS_PROCESS                    : 15324:6884
TRANSACTIONID                 : 2100200013000000
SCN                           : 14816546053214
SQL_BIND                      :
SQL_TEXT                      :
OBJ_EDITION_NAME              :
DBID                          : 473612098
RLS_INFO                      :
CURRENT_USER                  : MCDONAC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.