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?

Solution 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

Solution 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");

Comments

[dogbane]

OP wants to know where the values myhost, 1521 and orcl come from.

[BalusC]

@dogbane: As stated in the documentation, myhost is the DB host (hostname of machine where DB server runs, an IP address is also applicable and faster, if it's the same machine as where Java code runs, then use localhost or 127.0.0.1), 1521 is the DB port (TCP/IP port where DB is listening on, if you didn't change anything during DB setup, it defaults to 1521), orcl is the databasename (as you used in CREATE DATABASE).

[JakeRobb]

Note that "ORA11" occurs twice in the example: once as the name of the entry (the very beginning of the example) and once as the value of SERVICE_NAME. Oracle does not require these to be the same, and it's the SERVICE_NAME value that belongs in your connection string.