Connect local SQL Developer to Oracle Cloud Autonomous Database (Always Free Tier)

0

In a recent article I described how to provision an instance of Oracle Cloud Autonomous Data Warehouse in the recently launched Always Free Tier of OCI. This article shows how to connect from SQL Developer (desktop tool) to this instance. Note: connecting from SQL Developer is the same [of course] whether the database instance is in the free tier, part of a cloud trial or a regular paid for instance.

If you do not already have a 19.x version of SQL Developer, I suggest you first install that. You can download latest (19.2) version of SQL Developer for free from this location: https://www.oracle.com/tools/downloads/sqldev-v192-downloads.html (you need an Oracle account and you must accept the OTN license agreement)

Install SQL Developer 19.2 – by unzipping to a directory of your choice. Run SQL Developer by running the sqldeveloper.exe file.

image

In your Oracle Cloud Infrastructure dashboard, navigate to your ADW (or ATP) instance. From the DB Connections popup on the ADW Dashboard in OCI, click on Download for the Client Credentials (Wallet) – a zip file:

image

Provide the password for the Admin user:

image

The zip file with client credentials is download.

imageIn SQL Developer, create a new Connection (note: do not create a connection of type Cloud Connection – that type refers to the Schema as an Instance service, not to ATP or Adw):

image

Complete the connection wizard:

SNAGHTML3868b653

  • Provide a name for the connection – you can use spaces and other characters.
  • Set Database Type to Oracle.
  • Provide Username and Password – for example for the admin account that was created when the ATP or ADW instance was provisioned.
  • Select default for role – not SYSDBA or something similar
  • Select Cloud Wallet for connection type (note: this option was not available in SQL Developer 18.2 – the version that I tried to use in my first attempt)
  • In the field Configuration File browse for the zip file with client credentials that was downloaded from the ADW DB Connection popup page.
  • Select one of the options for Service – at this stage it does not matter much which one you select.

Click on button Test to verify your settings. When the result is Success you have succeeded and may proceed.

In the Connections tab, open the newly created connection.

image

You get your first glimpse into the ADW or ATP instance (running in the cloud) from the client side desktop comfort of your own laptop.

You can now make use of all facilities in SQL Developer to import and export data, model database objects, manage objects such as users, jobs, privileges and PL/SQL program units. The location of the database is not really relevant.


About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.