Connecting Remotely to the Database by Using Oracle SQL Developer

You can define an Oracle SQL Developer connection to your database in the cloud to perform operations as you would with an on-premises database.

How you make a SQL Developer connection to the Oracle Database depends on whether the Oracle Net Listener port has been unblocked. When a Database Cloud Service database deployment is created on Oracle Cloud Infrastructure Classic, the Oracle Net Listener port is blocked to ensure network security. For information about unblocking a port, see Enabling Access to a Compute Node Port.

Before You Begin

Before you use Oracle SQL Developer to connect to a database deployment, you need the following:

  • The IP address of the compute node

    The IP address of a compute node associated with a database deployment on Oracle Database Cloud Service is listed on the Oracle Database Cloud Service Overview page. For instructions to display this page, see Viewing Detailed Information for a Database Deployment.

  • The SSH private key file that matches the public key associated with the deployment.

Creating a SQL Developer Connection When the Listener Port Is Unblocked

To create a SQL Developer connection to a database deployment when the Oracle Net Listener port is unblocked:

  1. Open SQL Developer. Right-click Connections and select New Connection.

    Note:

    If you are using a version of SQL Developer in which the Connections panel shows both "Connections" and "Cloud Connections", right-click Connections. Do not right-click Cloud Connections, which is for Oracle Database Exadata Express Cloud Service.

    The New / Select Database Connection dialog appears.

  2. Provide the following information and then click Test.

    • Connection Name: Create a name for this connection.

    • Username: Name of the database user for the connection. This user must have sufficient privileges to perform the tasks that you want to perform while connected to the database, such as creating, editing, and deleting tables, views, and other objects.

    • Password: Provide the "Administration" password that you specified when you created the database deployment.

    • Hostname: Provide the Public IP address for the database deployment compute node you are connecting to.

    • Port: Provide the listener port number that you specified when you created the database deployment.

    • SID or Service Name: If you are connecting to Oracle Database 11g (non-CDB) or Oracle Database 12c or later (CDB), provide the SID. If you are connecting to an Oracle Database 12c or later pluggable database (PDB), provide the service name instead of the SID.

  3. If your test results show success, click Connect. You have connected SQL Developer to your database deployment in Oracle Database Enterprise Cloud Service. Now you can use SQL Developer as you normally would with an on-premises database.

Creating a SQL Developer Connection When the Listener Port Is Blocked

If the listener port has not been unblocked by enabling the ora_p2_dblistener access rule, you can define an SSH connection in Oracle SQL Developer 4.0.3 or later, with functionality to connect to a database through port forwarding. In that case, you will not need to follow the instructions in Enabling Access to a Compute Node Port.

  1. From the View menu, select SSH.

  2. In the SSH Hosts navigation panel, right click SSH Hosts and select New SSH Host.

  3. In the New SSH Host dialog:

    • Enter a name for the SSH Host.

    • In the Host field, enter the IP address of your database deployment.

    • In the Username field, enter oracle or opc.

    • Check Use key file, and browse for your private SSH key file.

    • Select Add a Local Port Forward.

    • Leave the Name field as Default. Leave the Host field as localhost. Set the Port field to the listener port number that you specified when you created the database deployment. Keep the default of Automatically assign local port.

    • Click OK.