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.
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:
Provide the password for the Admin user:
The zip file with client credentials is download.
In 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):
Complete the connection wizard:
- 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.
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.
Ignore my last comment, I have to use the user pasasword and not the wallet password,
This raises the question : What is the wallet password for ?
I can’t seem to have a successful Test Connection. Is there anything else that I need to setup on my Free Tier account? Or should I wait for a couple of hours since my ATP is successfully provisioned jus few minutes earlier.
Hi lucas. Do you know how can one connect using sql*plus?
Hi, I successfully connected my DB as ADMIN thanks to this article. But I have an issue. I am getting ‘”insufficient privileges” error while using this basic query;
select *
from APEX_190100.WWV_FLOW_FND_USER
Can you guide me please how can I set my users’ privileges?