Connect to Database

See previous posts for setting up your Always Free Account and Creating your Database.

This page will show how you can connect to the database using SQL Developer and Python. The first thing you need to do is to download the Database Client Wallet. This contains the client credentials and connection information to connect to your database.

On the Database Services screen click on the ‘DB Connection’ button.

The various connection details are displayed. You will need to Download the Client Credentials (Wallet). Click on the Download button. Save the file to your computer.

You will be prompted to enter Admin account password, which you entered when creating the database.

Open SQL Developer and create a new connection. Enter details for

  • Connection Name : this is a free format text field. Just give a meaningful name for the database
  • Username : our first connection will be to the ADMIN schema (DBA)
  • Password : this is the ADMIN password created when creating the database
  • Connection Type : select Cloud PDB from the drop down list
  • Configuration File : select the download zip file

Click on the Test button to test if you have entered everything correctly. If you have, you should see ‘Status : Success’ in the bottom left hand corner of the window. Otherwise you will see an error message, which means you have entered something incorrectly.

If everything is entered correctly then click on the ‘Connect’ button to create the connection and start using the Database.

WARNING: It isn’t a good idea to use the ADMIN (DBA) user for your database for general database/development work. Create a new user/schema and then use it.

Let’s create a new database user/schema for development called  ‘dev_user’

While connected to the ADMIN schema in SQL Developer, expand the connection node to display the object tree.

Right click the ‘Other Users’ object and select ‘Create User’ from the pop-up menu. A ‘Create User’ wizard will open. Enter the following.

  • First screen : Username, Password (needs 12 characters), default tablespace and temporary tablespace

On the ‘Granted Roles’ Tab, tick the check-box for the Roles,  CONNECT and RESOURCE.

On the Quotas tab, select it to unlimited for the DATA tablespace.

Then the ‘Apply’ button at the bottom of the window.  Your new developer schema has been created.

You should use this user/schema for all development work.

Next create a Database Connection in SQL Developer.  Follow the steps above for creating a connection but this time enter the ‘dev_user’ for the username and enter it’s password.

You can also use Python to connect to the database. Before setting up the connection, unzip the downloaded Oracle Client Credentials Wallet file, and remember where this directory is. You will also need to install Oracle Client and the cx_oracle library. See this page for more details.

Earlier in this post I show how you can download the wallet for your Always Free Autonomous Database. This zip file was used in SQL Developer.  To use the wallet for Python, you need to unzip this file. The unzipped directory will have a number of files. There are 2 of these that are important for you. The first is the sqlnet.ora file.  You need to edit this file to include the full directory path to the unzipped folder. For example, here is the contents of the sqlnet.ora file with the modified directory. I’ve highlighted the part I modified. You need to change this to reflect the directory on your computer.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/Users/brendan.tierney/Dropbox/Wallet_ATP")))

Now you are ready to start up Python and connect to your Always Free Autonomous Database

#connect to my Oracle Always Free Database
import cx_Oracle
import os

#set the OS environment variable to point to tnsnames.ora file
os.environ['TNS_ADMIN'] = "/Users/brendan.tierney/Dropbox/Wallet_ATP"

#create the connection. parameters are: username, password, and name of database services
#   database service names are listed in the tnsnames.ora file
con = cx_Oracle.connect('**********', '**********', 'atp_high')

WARNING: if you get an error message about not finding the service name, for example, ORA-12154: TNS:could not resolve the connect identifier specified, then set the environment variable TNS_ADMIN before starting Python. This will generally fix this issue. For example,

export TNS_ADMIN=/Users/brendan.tierney/Dropbox/Wallet_ATP