Oracle Database 11g Old Feature: Internet Addressing

No, this is not an Oracle database 11g new feature. If you are disappointed please move one to your next item on your reading list. Otherwise, the following is a quick refresher about a handy feature that has existed since 8.1.7 and will continue to exist in 11g. It is the Oracle supplied UTL_INADDR PL/SQL package.

UTL_INADDR is a neat utility for internet addressing. It provides two functions to retrieve host names and IP addresses of local and remote hosts.

Go ahead and try the following examples in your Oracle environment:

-- Give me the name of the machine that hosts 
-- the database server I'm connected to.
SELECT UTL_INADDR.get_host_name
  FROM DUAL;

-- Give me the IP address of the machine that hosts the 
-- database server I'm connected to.
SELECT UTL_INADDR.get_host_address
  FROM DUAL;

-- Give me the IP address of oracle.com, or any host name you desire
SELECT UTL_INADDR.get_host_address ('oracle.com')
  FROM DUAL;

-- Give me the host name of 141.146.8.66, or any IP address you desire
SELECT UTL_INADDR.get_host_name ('141.146.8.66')
  FROM DUAL;

-- Give me the IP addresses of all client machines connected to the database
SELECT DISTINCT machine,
                UTL_INADDR.get_host_address (SUBSTR (machine,
                      INSTR (machine, '\') + 1)) ip
           FROM gv$session
          WHERE TYPE = 'USER'
            AND username IS NOT NULL;

And here are a couple of related functions:

-- Give me the IP address of the client machine that 
-- I'm currently using to connect to the database.
SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
  FROM DUAL;

-- Give me the operating system of the server machine that 
-- hosts the database I'm connected to.
SELECT DBMS_UTILITY.port_string
  FROM DUAL;

Watch this space for more 11g old features…

Sources and resources:

Possibly related:

Tagged | Post a Comment