How do you find out the Oracle database's URL?

Question

How can I find out the URL and port for an Oracle database?

Example:

"jdbc:oracle:thin:@host:port:dbName","userName", "password");

Is there an SQL command or log/configuration file I can look at?

Answer 1

With oracle, there is a tnsnames.ora file which defines database addresses. This file is normally found in $ORACLE_HOME/network/admin and is used by oracle clients like sqlplus or Toad. Here is a sample tns entry:

ORA11 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ORA11)
    )
  )

From this entry you can work out that your jdbc connection string would be:

jdbc:oracle:thin:@hostname:1521:ORA11

Answer 2

By reading the documentation which came along with the JDBC driver in question.

In case of the Oracle JDBC thin driver, you can find it here.

Specifying a Database URL, User Name, and Password

The following signature takes the URL, user name, and password as separate parameters:

getConnection(String URL, String user, String password);

Where the URL is of the form:

jdbc:oracle:<drivertype>:@<database>

The following example connects user scott with password tiger to a database with INSTANCE_NAME orcl through port 1521 of host myhost, using the Thin driver.

Connection conn = DriverManager.getConnection
              ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");

If you want to use the default connection for an OCI driver, specify either:

Connection conn = DriverManager.getConnection 
              ("jdbc:oracle:oci:scott/tiger@");

or:

Connection conn = DriverManager.getConnection 
              ("jdbc:oracle:oci:@", "scott", "tiger");

For all JDBC drivers, you can also specify the database with a Oracle Net keyword-value pair. The Oracle Net keyword-value pair substitutes for the TNSNAMES entry. The following example uses the same parameters as the preceding example, but in the keyword-value format:

Connection conn = DriverManager.getConnection
              (jdbc:oracle:oci:@MyHostString","scott","tiger");

or:

Connection conn = DriverManager.getConnection
    ("jdbc:oracle:oci:@(description=(address=(host= myhost)
    (protocol=tcp)(port=1521))(connect_data=(INSTANCE_NAME=orcl)))",
    "scott", "tiger");