識別主機名和IP地址

文章來源：https://oracle-base.com/articles/misc/identifying-host-names-and-addresses
Identifying Host Names and IP Addresses

識別主機名和IP地址
This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.
該篇文章展示了一系列關於識別Oracle客戶端和伺服器主機名和IP地址的方法。

1、UTL_INADDR
2、SYS_CONTEXT
3、V$INSTANCE

4、V$SESSION

1、UTL_INADDR
The UTL_INADDR package was introduced in Oracle 8.1.6 to 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('bart') FROM dual;

UTL_INADDR.GET_HOST_ADDRESS('BART')
--------------------------------------------------------------
192.168.2.4

The IP address of the database server is returned if the specified host name is NULL or is omitted.
如果主機名未指定預設返回資料庫伺服器所在IP地址。

SQL> SELECT UTL_INADDR.get_host_address from dual;

GET_HOST_ADDRESS
--------------------------------------------------------------
192.168.2.5

An error is returned if the specified host name is not recognized.
如果指定的主機名不存在則返回錯誤。

SQL> SELECT UTL_INADDR.get_host_address('banana') from dual;
SELECT UTL_INADDR.get_host_address('banana') from dual
       *
ERROR at line 1:
ORA-29257: host banana unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

The GET_HOST_NAME function returns the host name of the specified IP address.

SQL> SELECT UTL_INADDR.get_host_name('192.168.2.4') FROM dual;

UTL_INADDR.GET_HOST_NAME('192.168.2.4')
--------------------------------------------------------------
bart

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

SQL> SELECT UTL_INADDR.get_host_name FROM dual;

GET_HOST_NAME
--------------------------------------------------------------
C4210gR2

1 row selected.

An error is returned if the specified IP address is not recognized.

SQL> SELECT UTL_INADDR.get_host_name('1.1.1.1') FROM dual;
SELECT UTL_INADDR.get_host_name('1.1.1.1') FROM dual
       *
ERROR at line 1:
ORA-29257: host 1.1.1.1 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

2、SYS_CONTEXT
The SYS_CONTEXT function is able to return the following host and IP address information for the current session:
SYS_CONTEXT函式可返回當前會話的以下資訊：
1） TERMINAL - An operating system identifier for the current session. This is often the client machine name.
2） HOST - The host name of the client machine.
3） IP_ADDRESS - The IP address of the client machine.
4） SERVER_HOST - The host name of the server running the database instance.
The following examples show the typical output for each variant.

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

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

1 row selected.

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

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

1 row selected.

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

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

1 row selected.

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

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

1 row selected.

3、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
------------------------------------------------
C4210gR2

1 row selected.

4、VSESSIONTheV

SESSION view contains the following host information for all database sessions:
1） TERMINAL - The operating system terminal name for the client. This is often set to the client machine name.
2） 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 = 'TIM_HALL';

TERMINAL                       MACHINE
------------------------------ -------------------------------
MARGE                          ORACLE-BASE\MARGE

1 row selected.