Connecting SQL Developer to Autonomous Data Warehouse and Creating Tables

Before You Begin

This tutorial shows you how to connect SQL Developer to a database in Oracle Autonomous Data Warehouse, and then create tables. This tutorial takes approximately 10 minutes to complete.

Oracle Autonomous Data Warehouse Cloud Tutorial Series

This is the second in a series of tutorials for Oracle Autonomous Data Warehouse. Perform the tutorials sequentially.

• Provisioning Autonomous Data Warehouse
• Connecting SQL Developer and Creating Tables
• Loading Your Data
• Running a Query on Sample Data
• Using Oracle Machine Learning with Autonomous Data Warehouse Cloud (set of additional tutorials)

Background

Oracle SQL Developer can connect to Oracle Autonomous Data Warehouse using a security credentials zip file that you created in the previous tutorial, or a credentials zip file provided by your Autonomous Data Warehouse administrator.

In this tutorial, you define a SQL Developer connection to a database in Autonomous Data Warehouse. You then use SQL Developer to create tables in the database. 

What Do You Need?

• Access to an instance of Autonomous Data Warehouse (see Using Autonomous Data Warehouse)
• Completion of previous tutorial, Getting Started with Oracle Autonomous Data Warehouse, in which the user adwc_user is created and a client credentials wallet zip file is downloaded
• The password for the data warehouse user adwc_user that was created in the previous tutorial

• Oracle SQL Developer (see Oracle Technology Network download site). Version 18.3 or later. Versions 18.2 or later contain enhancements for key Autonomous Data Warehouse features.
  Note:
  If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 8 included' distribution as it includes the files necessary to run SQL Developer and connect to your Autonomous Data Warehouse database.
  
  If you are using a SQL Developer version earlier than 18.2, see the documentation topic Connecting with Oracle SQL Developer (earlier than Version 18.2).
• A client credentials zip file and its keystore password, that you defined in the previous tutorial or obtained from your Autonomous Data Warehouse Cloud administrator. This file contains security credentials to connect SQL Developer to your Autonomous Data Warehouse Cloud database.

Define a SQL Developer Connection

First, define a SQL Developer connection as the user adwc_user to a database in your Autonomous Data Warehouse.

1. Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.
   Note:
   Do not right-click Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service. 
   

   

2. The New/Select Database Connection dialog appears. Enter the following information:
• Connection Name - Enter the name for this cloud connection.
• Username - Enter the database username . Use the adwc_user database account that you created in the previous tutorial.
• Password - Enter the password that you specified when creating the adwc_user in the previous tutorial.
  Note: Autonomous Data Warehouse requires strong passwords. The password you specify must meet the default password complexity rules. This 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 cannot contain the username.
• The password cannot be one of the last four passwords used for the same username.
• The password cannot contain the double quote (") character
• The password must not be the same password that is set less than 24 hours ago.
• Connection Type - Select Cloud Wallet.
• Configuration File - Click Browse, and select the path to your local copy of the Client Credentials zip file, downloaded from the Autonomous Data Warehouse service console by you, or given to you by your Autonomous Data Warehouse administrator.
• Keystore Password -
  If using SQL Developer 18.2.0 or newer:  Starting with version 18.2.0, SQL Developer does not require that you enter a Keystore Password and does not provide the Keystore Password field.
  If using a version of SQL Developer older than 18.2.0: Enter the password that was generated when you or your Autonomous Data Warehouse administrator downloaded the Client Credentials zip file from the Autonomous Data Warehouse console.
• Service -In the drop-down menu, service selections are prepended with database names. Select the low, medium or high menu item for your database. These service levels map to the LOW, MEDIUM, and HIGH consumer groups, which provide different levels of performance and concurrency.
  Note: Earlier versions of SQL Developer may not support this feature.



3. Click Test.
   Status: Success displays at the bottom left of the New/Select Database Connection dialog.
4. Click Connect.
   An entry for the new connection appears under Connections.

Create SH Tables in your Autonomous Data Warehouse Database

After you have connected SQL Developer to your Autonomous Data Warehouse database, use a SQL Developer worksheet to define CREATE TABLE statements to create the SH tables (sales history tables from an Oracle sample schema) in the adwc_user schema. In the next tutorial, you will load data into these tables from an object store.

1. Copy and paste this code snippet to a SQL Developer worksheet. Run the script to create the SH tables and click Commit. 
   

   

Examine the SH Tables that you Created

Now that you have created the SH tables, take a moment to examine them. In the next tutorial, you will load data into these tables from an object store.

1. The SQL Developer Script Output panel shows the tables that have been created in the user named adwc_user. 
   

   

2. Note that the new tables also appear in the SQL Developer Connections panel.
   

   

3. Examine the details of each column of the CHANNELS table.
   

   

4. Click the Data tab of the CHANNELS table. Note that so far, you have defined tables, but these tables are not yet populated with Data.
   

   

5. In the next tutorial, "Loading Your Data", you will load data from an object store into these SH tables.