Wednesday, September 18, 2019

Free Oracle Cloud: 3. Connecting with SQL Developer (Desktop) to ATP

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In the previous post we setup our Oracle database in the Free Autonomous Oracle Database Cloud.
Now we want to connect to this database from our machine with SQL Developer.

Login to your Oracle Cloud Account. In case you forgot the link, you can always go back to your emails and in the "Your Oracle Cloud Account is Fully Provisioned" you find a button to Sign in.

Typically it's in this format: console.DATA_CENTER_YOU_PICKED.oraclecloud.com/?tenant=YOUR_CLOUD_ACCOUNT?provider=OracleIdentityCloudService.
So in my case this is: https://console.us-ashburn-1.oraclecloud.com/?tenant=dimi&provider=OracleIdentityCloudService

Once in, navigate on the left hamburger icon to Autonomous Transaction Processing and select the Autonomous Database (see previous blog post - last two screenshots). In the overview screen click the DB Connection button.


A modal dialog appears to download the credentials (wallet). Click the Download button:


The modal dialog changes and you need to enter a password and hit download:


Now we will make a new connection in SQL Developer. Right click on the Oracle Connections and click the New Connection... link:


Enter a name of your choice. In the User Info section as Username you enter admin and as paswoord, the paswoord you entered when you setup your database (previous blog post).
Select for Connection Type Cloud Wallet. A details section will open where you can select the file you downloaded before in the Configuration File.


Hit the Test button and see if you receive a Success message. Once fine, you can save and connect.

The admin user is a special user in the Oracle Cloud. You can see it as the sys/system user, with many of the same rights. So you can query for example dba_users to see all the users/schemas in the database:


So now you are all set to play with your database. You can setup new users, create tables, load and query data etc.

In this post we saw how to connect from SQL Developer Desktop version to the Oracle Cloud Database. In the next post I will show how to connect with SQL Developer Web, a webbased alternative to SQL Developer (Desktop).

13 comments:

  1. I was able to connect successfully by following the steps in this post.

    Thanks for sharing knowledge.

    ReplyDelete
  2. again a great post Dimitri,
    any suggestions down the guide on how can we create our own tablespaces and do a datapump? (do we get a folder in the ATP to create our own tablespaces?)

    ReplyDelete
  3. The download of the wallet is not working, I provide the password, and when pressing the Download button, the wallet is not downloaded.

    ReplyDelete
  4. Great post - I didn't know about the 'Cloud Wallet' connection type in SQL Developer. Very cool.

    ReplyDelete
  5. Perfect instructions thanks Dimitri

    ReplyDelete
  6. The download wallet wasnt working for me either. It did work after I switched the Cloud console language to English.

    ReplyDelete
  7. Hi Dimitri - Awesome step by step instructions. The "Download Wallet" attempt is failing. I enter the wallet password but I see a brief "Error Generating" message and wallet doesn't download. Using latest "Mozilla" browser on the desktop and all PopUps turned off. Also, microsoft edge browser has same issues. Cannot download wallet .zip file from my ATP cloud login console area. Please advise for any ideas.

    ReplyDelete
  8. To download wallet - set your language to "english" (switch between 2 languages and set it back to english. Save your profile details (top right menu). also log out and log back into your cloud acct. to follow above instructions to download the wallet.zip file

    ReplyDelete
  9. Hi Dimitri,
    nice series of posts on "Free Oracle Cloud" !
    These decided me to make the jump and have a try.. !
    For the connection I followed your instructions, but when I try to connect to my ATP the connection is waiting, waiting and never connect.
    I have to stop sqldeveloper...
    What mistake did I make ?

    ReplyDelete
  10. If you follow the steps, maybe your net connection. I failed before, and try to connect in better net connection and finally succed.

    ReplyDelete
  11. Thanks Dimitri,
    I will try the connection at home with better bandwidth than at the office.

    ReplyDelete
  12. Is it possible to connect with PLSQL Developer?

    ReplyDelete
  13. I have oracle database on premise , and my idea is to create another autonomous database on oracle cloud for development and connect both the databases . How can i do that. We dont have the public IP. I want to use the VPN connection to connect our local DB on premise from cloud. How can i do this.

    ReplyDelete