Something for free (serverside variables on JDBC client)

Two code pieces below give the same result,
The 1st one doesn’t require network round-trip/additional SQL execution at all.

  • String sidKey = (((oracle.jdbc.driver.OracleConnection) connection)).getServerSessionInfo().getProperty(“AUTH_SESSION_ID”);
  • ResultSet rsSid = connection.createStatement().executeQuery(“select SYS_CONTEXT(‘USERENV’, ‘SID’) from dual”);
    int sid = 0;
    if (rsSid.next()) {
    sid = rsSid.getInt(1);
    }
  • A full list as of 11.2.0.4 Oracle database and ojdbc6.jar JDBC driver
"INSTANCE_NAME"
"AUTH_NLS_LXCSTMPFM"
"AUTH_VERSION_STATUS"
"AUTH_SC_SERVER_HOST"
"SESSION_NLS_LXCNCHAREXCP"
"SERVER_HOST"
"AUTH_NLS_LXCTTZNFM"
"AUTH_SVR_RESPONSE"
"AUTH_FAILOVER_ID"
"AUTH_SERIAL_NUM"
"AUTH_NLS_LXLAN"
"AUTH_SC_INSTANCE_START_TIME"
"AUTH_SC_INSTANCE_NAME"
"AUTH_DBNAME"
"AUTH_SC_DB_DOMAIN"
"SESSION_NLS_LXCCHARSET"
"AUTH_DB_ID"
"AUTH_INSTANCENAME"
"AUTH_VERSION_SQL"
"AUTH_NLS_LXCTERRITORY"
"SESSION_TIME_ZONE"
"AUTH_INSTANCE_NO"
"DATABASE_NAME"
"AUTH_SC_SERVICE_NAME"
"AUTH_SERVER_PID"
"AUTH_NLS_LXCSORT"
"AUTH_NLS_LXCISOCURR"
"AUTH_VERSION_NO"
"AUTH_NLS_LXCDATELANG"
"SERVICE_NAME"
"AUTH_NLS_LXCSTZNFM"
"AUTH_SC_DBUNIQUE_NAME"
"AUTH_NLS_LXCDATEFM"
"AUTH_NLS_LXCUNIONCUR"
"AUTH_VERSION_STRING"
"AUTH_SESSION_ID"
"SESSION_NLS_LXCNLSLENSEM"
"AUTH_NLS_LXCNUMERICS"
"AUTH_XACTION_TRAITS"
"AUTH_SC_INSTANCE_ID"
"AUTH_SC_SVC_FLAGS"
"AUTH_NLS_LXCTIMEFM"
"AUTH_DB_MOUNT_ID"
"AUTH_CAPABILITY_TABLE"
"AUTH_USER_ID"
"AUTH_NLS_LXCCURRENCY"
"AUTH_NLS_LXCCALENDAR"
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s