, , , , , ,

How to Get IP address and host address in Oracle

This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

    UTL_INADDR

    The UTL_INADDR provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.
    The GET_HOST_ADDRESS function returns the IP address of the specified host name.
    SQL> SELECT UTL_INADDR.get_host_address('TEST') FROM dual;

    UTL_INADDR.GET_HOST_ADDRESS('TEST')
    --------------------------------------------------------------------------------
    192.167.1.56

    SQL>

     The IP address of the database server is returned if the specified host name is NULL or is omitted.

    SQL> SELECT UTL_INADDR.get_host_address from dual;

    GET_HOST_ADDRESS
    --------------------------------------------------------------------------------
    192.161.1.55

    SYS_CONTEXT

    The SYS_CONTEXT function is able to return the following host and IP address information for the current session:
    • TERMINAL – An operating system identifier for the current session. This is often the client machine name.
    • HOST – The host name of the client machine.
    • IP_ADDRESS – The IP address of the client machine.
    • SERVER_HOST – The host name of the server running the database instance.
    SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

    SYS_CONTEXT('USERENV','TERMINAL')
    --------------------------------------------------------------------
    TEST10

    SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

    SYS_CONTEXT('USERENV','IP_ADDRESS')
    --------------------------------------------------------------------
    192.167.1.55

    SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

    SYS_CONTEXT('USERENV','SERVER_HOST')
    --------------------------------------------------------------------
    Z4210gr11

    V$INSTANCE

    The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.
    SQL> SELECT host_name FROM v$instance;

    HOST_NAME
    ------------------------------------------------
    Z4210gR11

    V$SESSION

    The V$SESSION view contains the following host information for all database sessions:

    TERMINAL – The operating system terminal name for the client. This is often set to the client machine name.

    MACHINE – The operating system name for the client machine. This may include the domain name if present.

    The following examples show the typical output for each column.

    SQL> SELECT terminal, machine FROM v$session WHERE username = 'OEAG';

    TERMINAL                       MACHINE
    ------------------------------ ----------------------------------------------------
    TEST10                          ORACLE-BASETEST10

    /by
    , , , ,

    R12 – Meaning for Concurrent Request Status Codes and Phase Codes

    Concurrent Request Phase Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_PHASE_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    C
    Completed
    I
    Inactive
    P
    Pending
    R
    Running

    Concurrent Request Status Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_STATUS_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    R
    Normal
    I
    Normal
    Z
    Waiting
    D
    Cancelled
    U
    Disabled
    E
    Error
    M
    No Manager
    C
    Normal
    H
    On Hold
    W
    Paused
    B
    Resuming
    P
    Scheduled
    Q
    Standby
    S
    Suspended
    X
    Terminated
    T
    Terminating
    A
    Waiting
    G
    Warning

    Normally a concurrent request proceeds through three, possibly four, life cycle stages or phases,

    Phase Code
    Meaning with Description
    Pending
    Request is waiting to be run
    Running
    Request is running
    Completed
    Request has finished
    Inactive
    Request cannot be run

    Within each phase, a request’s condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

    The status and the description of each meaning given below:

    Phase
    Status
    Description
    PENDING
    Normal
    Request is waiting for the next available manager.
    Standby
    Program to run request is incompatible with other program(s) currently running.
    Scheduled
    Request is scheduled to start at a future time or date.
    Waiting
    A child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.



    RUNNING
    Normal
    Request is running normally.
    Paused
    Parent request pauses for all its child requests to complete. For example, a report set pauses for all reports in the set to complete.
    Resuming
    All requests submitted by the same parent request have completed running. The Parent request is waiting to be restarted.
    Terminating
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    COMPLETED
    Normal
    Request completes normally.
    Error
    Request failed to complete successfully.
    Warning
    Request completes with warnings. For example, a report is generated successfully but fails to print.
    Cancelled
    Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
    Terminated
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    INACTIVE
    Disabled
    Program to run request is not enabled. Contact your system administrator.
    On Hold
    Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
    No Manager
    No manager is defined to run the request. Check with your system administrator.
    /by