Connect with SQL Developer and Create a View for Data Visualization

Before You Begin

This tutorial shows you how to download Autonomous Database connection credentials and create a secure connection between SQL Developer and an Autonomous Database. With your connection you create a database user, ANALYST1, and create a view to the SH table in the database. This tutorial takes approximately 10 minutes to complete.

Oracle Autonomous Database Visualization Tutorial Series

This tutorial is the first in a series for connecting and visualizing your data with Oracle Autonomous Database. Perform the tutorials sequentially.

Provision your Autonomous Database:
- Provisioning Autonomous Data Warehouse
- Provisioning Autonomous Transaction Processing
Connect with SQL Developer and Create a View for Data Visualization
Create Your First Visualization with Data From an Oracle Autonomous Database

Background

Oracle SQL Developer connects to an Autonomous Database using a security credentials zip file. You create the security credentials zip in the first section or get it from your administrator.

In this tutorial, you define a SQL Developer connection to an Autonomous Database. Use SQL Developer to create a user and create a view from the SH sample table.

What Do You Need?

Access to an instance of an Autonomous Database: either Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing.
Obtain the password for the Autonomous Database Admin user.

Download Oracle SQL Developer from Oracle Technology Network download site. Download version 18.2 or later, because this version contains enhancements for key Autonomous Database features.
If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 9 included' distribution as it includes the files necessary to run SQL Developer and connect to your Autonomous Data Warehouse Cloud database.

Download Client Credentials Zip File

In this section you download a client credentials zip file for the Autonomous Database instance that you want to connect to. If you already have the client credentials file for your Autonomous Database, skip this step (your administrator can provide this file). The client credentials file contains security-related files, including a wallet file with certificates for Secure Sockets Layer (SSL) communication between the Autonomous Database and Oracle Data Visualization.

Access Oracle Cloud Infrastructure with your URL (web address). On the Oracle Cloud Infrastructure sign-in page, in the Cloud Tenant ID field enter a tenant.
In Oracle Cloud Infrastructure, enter your user name and password, and click Sign In.
On the Oracle Cloud Infrastructure home page, at the top left, click MENU.

Description of the illustration oci_console_menu.png
Select Autonomous Data Warehouse.
Under List Scope, from the Compartment list, select your compartment or the root compartment.
Under Name, click a database instance.
Click DB Connection.

Description of the illustration adw_console_db_connection
In Database Connection, click Download.

Description of the illustration adw_console_db_download
In the Download Wallet dialog enter an encryption password for the wallet, confirm the password, and then click Download.

Description of the illustration adw_db_download_password
Click Save File, and then click OK.

Define a SQL Developer Connection

In this section, define a SQL Developer connection as the Admin user.

Open SQL Developer. In the Connections panel, right-click Connections and select New Connection.

Description of the illustration select_new_connection
The New/Select Database Connection dialog appears. Enter the following:
- Connection Name - Enter a description for the connection.
- Username - Enter the database user name. Use Admin or another user with equivalent privileges.
- Password - Enter the password for the specified user.
From Connection Type, select Cloud PDB.
From the Configuration File list, click Browse, and select the path to your local copy of the Client Credentials zip file. Select wallet_SALESADB.zip.
From the Service list, select the _high menu item: salesadb_high.

select new connection dialog — Description of the illustration new_select_database_connection

Click Test. When the status is Success, click Connect.

Create a User `Analyst1`

After you connect SQL Developer to your Autonomous Database, use a SQL Developer worksheet to define a create user statement to create analyst1. You can use this user to connect to your Autonomous Database and load data for data visualization and data analysis.

Open a SQL Developer worksheet and run the following SQL statements to create analyst1, swapping in a password that complies with the password complexity rules.
```
create user analyst1 identified by "<password>;";

grant dwrole to analyst1;
```
Autonomous Data Warehouse requires strong passwords. The password you specify must meet the default password complexity rules. The database checks for the following requirements when you create or modify passwords:
- The password must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
- The password can't contain the username.
- The password can't be one of the last four passwords used for the same username.
- The password can't contain the double quote (") character.
- The password must not be the same password that is set less than 24 hours ago.