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