CODEWITHSUNDEEP
javaoracle-databasejdbc
Andreas
Andreas

Reputation: 1203

Get active Host and Port from jdbc connection to oracle server (don't parse url)

is there a way to get connected host and port of a JDBC connection to a Oracle DB Server?

I know, i could parse the URL. But we use failover and i want to know, which server i'm actually connected with. Parsing of URLs is static and error prone because of the different formats we us.

Couldn't find it in the connection metadata.

With 'select * from global_name' i could get the servicename. But i haven't found a way to get the host and port, we are connected to.

Any idea?

Upvotes: 2

Views: 8845

Answers (4)

kab00m
kab00m

Reputation: 1

try one of:

select sys_context('userenv','instance_name') from dual;
select sys_context('userenv','instance') from dual;
select sys_context('userenv','server_host') from dual;

Documentation: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

Upvotes: 0

Sigal Shaharabani
Sigal Shaharabani

Reputation: 450

This should give you the host name of the instance you're running from:

select host_name from v$instance

Upvotes: 1

tbone
tbone

Reputation: 15493

Not sure about the port (which is really the port on the listener), but for host you might try:

select utl_inaddr.get_host_name, utl_inaddr.get_host_address from dual;

There are other options on db side (such as querying v$instance), see here. For example, the SID you might use:

select sys_context('userenv','instance_name') from dual;

Hope that helps. Also note that I use utl_inaddr, but only for logging and email/alerting purposes.

EDIT:

Also, be aware that there are some concerns with spoofing Oracle session info, partly why I noted that I only use this to add context to alerts and logs. I don't pretend to know all the ins/outs, but basically this issue concerns a client pretending to be someone (or from somewhere) else. Since you're controlling the connection via your java app, it shouldn't be a concern, but see here for more if you care.

Upvotes: 2

asa
asa

Reputation: 4040

here are some info you can get from a Connection:

            Connection dbConnection = null;
        try {
            dbConnection = dataSource.getConnection();
            DatabaseMetaData dbMetaData = dbConnection.getMetaData();
            getLogger().debug("DB Product Name   = " + dbMetaData.getDatabaseProductName());
            getLogger().debug("DB Product Version= " + dbMetaData.getDatabaseProductVersion());
            getLogger().debug("DB Driver Name    = " + dbMetaData.getDriverName());
            getLogger().debug("DB Driver Version = " + dbMetaData.getDriverVersion());
            getLogger().debug("DB Username         = " + dbMetaData.getUserName());
            getLogger().debug("DB URL            = " + dbMetaData.getURL());
        } catch (Exception e) {
            getLogger().debug("Failed to recover DatabaseMetaData: "+e.getMessage(), e);
        } finally {
            if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (Exception ex) {
                    getLogger().error("Failed to close the DB connection: "+ex.getMessage(), ex);
                }
            }
        }

Check the DatabaseMetaData if you need more/different info. Example of output:

DB Product Name   = Oracle
DB Product Version= Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
DB Driver Name    = Oracle JDBC driver
DB Driver Version = 11.2.0.2.0
DB Schema         = XXXXX
DB URL            = jdbc:oracle:thin:@XXX.XXX.XXX.XXX:11010:MYSID

Upvotes: 3

Related Questions