DBA Hub

📋Steps in this guide1/4

Identifying Host Names and IP Addresses

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 miscconfigurationintermediate
by OracleDba
26 views
1

UTL_INADDR

The 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 function returns the IP address of the specified host name. The IP address of the database server is returned if the specified host name is NULL or is omitted. An error is returned if the specified host name is not recognized. The function returns the host name of the specified IP address. The host name of the database server is returned if the specified IP address is NULL or omitted. An error is returned if the specified IP address is not recognized.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SQL> SELECT UTL_INADDR.get_host_address('bart') FROM dual;

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

SQL>

SQL> SELECT UTL_INADDR.get_host_address from dual;

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

SQL>

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


SQL>

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

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

SQL>

SQL> SELECT UTL_INADDR.get_host_name FROM dual;

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

1 row selected.

SQL>

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


SQL>
2

SYS_CONTEXT

The function is able to return the following host and IP address information for the current session: - - An operating system identifier for the current session. This is often the client machine name. - - The host name of the client machine. - - The IP address of the client machine. - - The host name of the server running the database instance. The following examples show the typical output for each variant.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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.

SQL>
3

V$INSTANCE

The column of the view contains the host name of the server running the instance.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SQL> SELECT host_name FROM v$instance;

HOST_NAME
------------------------------------------------
C4210gR2

1 row selected.

SQL>
4

V$SESSION

The view contains the following host information for all database sessions: - - The operating system terminal name for the client. This is often set to the client machine name. - - 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. For more information see: - Identifying Host Names and IP Addresses in SQL and PL/SQL - UTL_INADDR - SYS_CONTEXT - V$INSTANCE - V$SESSION Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SQL> SELECT terminal, machine FROM v$session WHERE username = 'TIM_HALL';

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

1 row selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!