Before You Begin
This tutorial shows you how to log in to the Oracle Autonomous Data Warehouse Cloud console, create a service instance, and create a user. This tutorial takes approximately 15 minutes to complete.
Oracle Autonomous Data Warehouse Cloud Tutorial Series
This is the first in a series of tutorials for the Oracle Autonomous Data Warehouse Cloud. Perform the tutorials sequentially.
- Provisioning Autonomous Data Warehouse Cloud
- 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)
Oracle Autonomous Data Warehouse Cloud is fully-managed, offers high-performance, and is elastic. You have all of the performance of the market-leading Oracle Database in a fully-managed environment that is tuned and optimized for data warehouse workloads. Autonomous Data Warehouse Cloud supports all standard SQL and business intelligence (BI) tools and delivers scalable analytic query performance.
In this tutorial, you create a service instance, connect SQL Developer to Autonomous Data Warehouse Cloud, and create a database end user to use in all subsequent tutorials.
What Do You Need?
- Subscription to Oracle Autonomous Data Warehouse Cloud
Oracle Cloud user name, password, and
Locate your account details in the New Account Information email that you received from Oracle Cloud when your user account was set up. If you don't have your New Account Information email, contact your account administrator.
- Tenancy in Oracle Cloud Infrastructure, including the creation of a compartment and the setting of access to resources (see Oracle Cloud Infrastructure documentation for setting up your tenancy).
- Oracle SQL Developer (see Oracle
Technology Network download site).
We recommend that you download version
18.2.0 or later, because this version
contains enhancements for key Autonomous
Data Warehouse Cloud features. SQL
Developer 17.3.1 will also work with
Autonomous Data Warehouse Cloud; versions
earlier than 17.3.1 will not.
If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 9 included' distribution as it includes both Java 9 and the Java Cryptography Extension (JCE) files necessary to run SQL Developer and connect to your Autonomous Data Warehouse Cloud.
If you are a non-Windows user, download and install the appropriate Java 8 JDK or later version for your Operating System.
Create an Instance
The first step is to sign in to the Autonomous Data Warehouse Cloud and create an instance.
- Sign in from https://cloud.oracle.com/home. On the Oracle Cloud page, click Sign In.
- On the Cloud Account page, enter your
cloud account name and click My
- Enter your User Name and Password
and click Sign In to sign in to
your Oracle Cloud Account.
- If you see the Set Preferences page, enter your Language and Timezone and click OK.
- On the Cloud My Services page, click the
navigation menu in the upper left to show
top level navigation choices.
- Click Services to show the
- Scroll in the list of available services and select Autonomous Database from the list.
- In the Oracle Cloud Infrastructure page, click the menu in the upper left to show the available services.
- From the left navigation pane, click Autonomous
Note: You can create a bookmark so that Autonomous Data Warehouse will appear on the Oracle Cloud Infrastructure page without needing to open the navigation pane.
- The console for Autonomous Data
Warehouse displays. You can use the List Scope
drop-down menu to select a compartment; in
this example the
SQLDevcompartment is selected.
Although this console shows that no data warehouses have yet been created, if there were a long list of data warehouses, you could filter the list by using the Filters drop-down menu to filter by the state of the data warehouse (available, stopped, terminated, and so on.)
Click Create Autonomous Data Warehouse.
- The Create Autonomous Data Warehouse dialog appears. At the top of the dialog, click the Click here link to select a compartment for the data warehouse.
- In the Create Autonomous Data Warehouse
dialog, enter the following information:
- Display Name - Enter a name for the data warehouse for display purposes.
- Database Name - Use letters and numbers only, starting with a letter. Maximum length is 14 characters. (Underscores not initially supported.)
- CPU Core Count - Number of CPUs for your service.
- Storage (TB) - Select your storage capacity in terabytes. It is the actual space available to your service instance, including system-related space allocations.
- Administrator Credentials- Password for ADMIN user of the service instance. The password must meet the following requirements:
- The password must be between 12 and 60 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
- The password cannot contain the double quote (") character.
- The password must be different from the last 4 passwords used.
Type - select whether you are
bringing your own license or
subscribing to new database software
- Tags - (Optional) Tagging is
a metadata system that allows you to
organize and track resources within
your tenancy. Tags are composed of
keys and values which can be attached
Click Create Autonomous Data Warehouse.
- The Create Autonomous Data Warehouse
dialog closes. On the console, the State
field indicates that the data warehouse is
provisioning. When creation is completed, the State field changes from
Download the Credentials Zip File
Once you have created the data
warehouse, download the credentials zip
file for client access to that data
warehouse. You will use this file in the
next tutorial to connect SQL Developer to
your Autonomous Data Warehouse database.
- In the console, in the menu across
from the name of your new data
warehouse, select Service
- A dialog opens to sign in to the
service console. In the Username
field, enter the following
- Username -
admin(the default administrative user named
adminthat was created for your Autonomous Data Warehouse Cloud account)
- Password - the administrator password you specified when you created your service instance
- Username -
- The service console opens in the Overview mode.
in the left navigation pane.
- Click Download
- You are prompted to create a password
for the credentials zip file. Enter a
password and click Download.
- Store the zip file and make note of
the password. You will use the zip file
and password in the next step to define
a SQL Developer connection to your
Autonomous Data Warehouse Cloud
Note: Version 18.2.0 of SQL Developer drops the requirement to enter the Autonomous Data Warehouse keystore password and does not provide the keystore password field in the dialog for creating a connection. If using SQL Developer version 18.2.0 or newer, you do not use this password.
Define a SQL Developer Connection
First, define a SQL Developer connection to the database in your Autonomous Data Warehouse Cloud service.
- Open SQL Developer on your local
computer. In the Connections panel,
and select New
Depending on your version of SQL Developer, do not right-click Cloud Connection or Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service.
- 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 default
administrator database account (
admin) that is provided as part of the service.
- Password - Enter the
adminuser's password that you or your Autonomous Data Warehouse Cloud administrator specified when creating the service instance.
- Connection Type - Select Cloud PDB.
- Configuration File - Click Browse, and select the Client Credentials zip file, downloaded from the Autonomous Data Warehouse Cloud service console by you, or given to you by your Autonomous Data Warehouse Cloud 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 Transaction Processing administrator downloaded the Client Credentials zip file from the Autonomous Data Warehouse Cloud 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.
Status: Success displays at the left-most bottom of the New/Select Database Connection dialog.
An entry for the new connection appears under Connections.
Create a User in your Autonomous Data Warehouse Cloud Database
Once you have connected SQL Developer to
your Autonomous Data Warehouse Cloud
database, use a SQL Developer worksheet to
statement to create the user
In the next tutorial, you will create
sales history tables in the
schema and load data into these tables
from an object store.
- Open a SQL Developer worksheet and run
the following SQL statements to create
adwc_user, swapping in a password with the guildelines provided in the following Note section.
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:
create user adwc_user identified by "<password>";
grant dwrole to adwc_user;
- 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
Note: Autonomous Data Warehouse Cloud databases come with a pre-defined database role named
This role provides the common privileges for a data warehouse user:
CREATE ANALYTIC VIEW, CREATE ATTRIBUTE DIMENSION, ALTER SESSION, CREATE HIERARCHY, CREATE JOB, CREATE MINING MODEL, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, READ,WRITE ONdirectory
DATA_PUMP_DIR, EXECUTEprivilege on the PL/SQL package
- In the next tutorial, "Connecting SQL Developer and
Creating Tables", you will connect SQL Developer to your
Autonomous Data Warehouse Cloud database as user
adwc_user, and define
SHtables (sales history tables from an Oracle sample schema) for that user. Later, you will load data into those tables from an Object Store.
Want to Learn More?
- Autonomous Data Warehouse Cloud website
- Documentation on how to add groups and users for Oracle Cloud Infrastructure through the Oracle Identity Cloud Service