Resetting Oracle Database Express to Default Tables Again

Overview

Purpose

This tutorial covers how to load information into an Oracle Database Cloud Service using SQL Developer, equally well as export data from your Database Cloud Service.

Fourth dimension to Complete

Approximately 20 minutes

Introduction

The Oracle Database Deject is an ideal platform for building data-centric applications universally available in the Cloud. These applications allow users to add together, modify and manipulate data to extract valuable data to allow for better operations and strategic decisions.

Some applications will begin with a clean slate – with data structures defined, but without any data populating those structures. The data is added through user interaction.

A more than mutual scenario is an awarding where some amount of data already exists, although not in an Oracle Database Deject Service. This scenario volition crave data to be loaded into the Oracle Database Cloud Service.

Yous tin load data into your Oracle Database Cloud Service with two dissimilar tools – Application Express or SQL Developer.

In Awarding Limited, SQL Workshop contains an area which allows you to run SQL scripts. These scripts tin can include both SQL statements and embedded data, and tin be used to load data into the Oracle Database Deject Service. In addition, Awarding Express has a set of utilities that allow you to load data from a diversity of formats, including .csv files, Excel spreadsheets and text files.
SQL Developer is a Java-based tool that runs on a customer machine. SQL Developer accesses an Oracle Database Cloud Service through a ready of RESTful Web Service calls and allows y'all to access and load information and data structures into your Oracle Database Cloud Service.

In this tutorial, you acquire how to load information into an Oracle Database Deject Service using SQL Developer, as well as export data from your Database Cloud Service from My Services.

Prerequisites

Before starting this tutorial, you should accept performed the Signing Upward for a Database Cloud Service tutorial.

Setting Up Your Database Cloud Service to Allow for Data Uploads from SQL Developer

There are three tasks you need to perform before you tin perform the data upload from SQL Developer.

Change the Password for the SFTP User

Y'all demand to change the password for the SFTP user defined for your Database Cloud Service. Perform the post-obit steps:

Review the e-mail you received when you created your Database Cloud Service. Identify the sftp user and Secure FTP Site in the e-mail.

Sign In to your Deject Service. Click **Sign In** from the main cloud page.

Make sure the correct Information Center is selected and click **Sign In to My Services**.

Login every bit the database administrator user for your service and click **Sign In**.

Enter * in the search field and click **Search**.

Select the SFTP user and click Reset Countersign.

Click the Manually change the password option and enter a new password that satisfies the password policy and click **Reset Password**.

The password was changed successfully. Side by side you need to create a a deject connectedness user in your database cloud service.

Create a Cloud Connectedness User in Your Database Deject Service

You demand to create a user to load the tables and data into your Database Cloud Service. Perform the following steps:

From the My Services window, click the Details icon.

Note the URL in the Host field. This is the URL you lot need to specify in the adjacent department when y'all create a connection to your cloud service. Click **Launch Service**.

Select **Administration** > **Create Users and Groups**.

Enter the required information, modify Require Change of Password on start User to No, and select **SQL Developer** from the list of User Groups. And then click **Create User**.

The user was created successfully. Next, y'all create a Cloud Connexion in SQL Developer.

Create a Cloud Connection in SQL Developer

You need to create a connectedness to your Database Cloud Service from SQL Developer. Perform the following steps:

Open up SQL Programmer. Note: You must have release three.2.x.09 or later.

From the Connections window, Right-click **Cloud Connections** and select **New Cloud Connexion...**

Enter the following information and click OK.

Connexion Name: <identity domain of your service>
Database Username: <name of user with SQL Developer user group>
Database URL: <url to database cloud service, from my services overview folio>
SFTP Username: <user from email that you changed password for>
SFTP Hostname: <sftp hostname from email>

The Cloud Connectedness was created. Aggrandize the node to connect.

Enter the password of the user you created in your database cloud service and click OK.

The connection to your Database Cloud Service was successful. You are now ready to load information into your Database Cloud Service..

Loading Data into your Database Cloud Service from SQL Developer

In this section, you add some tables from another database into your Cart and deploy to the cloud. Perform the following steps:

Open a connection to the database that contains the data you lot want to load. Drag the database objects to the cart. In this case, you drag the PROJECTS table to the cart. Y'all tin drag additional tables into the cart equally required.

You tin deploy the DDL and/or the Data and also specify a Where clause. In one case all the objects you desire to load are in the cart, click the Deploy Cloud icon.

Enter a **Title** (no spaces) and click **Replace existing destination objects** for Deploy DDL and click **Apply**. Note that the Deploy Data section is not available because you but just selected the DDL option (from the cart).

The file deploy.zip will be created and uploaded to the SFTP server and then executed into your database cloud service.

Expand **Deployment** and select the deployment you merely created. Review the details. You may need to click the Refresh button. When the status is Processed this means the deployment completed successfully.

If you lot review the list of tables now, yous run across the tables you just deployed in the list.

You can also switch to your database service and review SQL Workshop to see that the tables you deployed are now listed in the Recently Created Tables area.

Exporting Data from Your Database Cloud Service

In this section, you lot export your data from your Database Cloud Service. Perform the post-obit steps:

Switch to the browser where you lot brandish the Service details. Click the **Data Consign** tab.

Note that the Data Structions (DDL) will always be exported but you can choose whether you want to include the information. Click **Create Data Export**.

Notation the export was created. Click the Overview tab to view the SFTP user the export was uploaded to.

The SFTP Account is the aforementioned account that you lot reset the password for. Click the **Information Export** tab again.

Click **Refresh** to see that the status has changed to Available. Y'all can now view the dmp file using a SFTP tool such every bit Filezilla.

Connect the the SFTP Host with the Username and password. Annotation that the dmp file is contained in the download directory. Yous tin drag these files to your machine or whatever other want location

Summary

In this tutorial, you accept learned how to:

Set up upwardly your Database Cloud Service to allow for information uploads from SQL Developer
Load data into your Database Cloud Service from SQL Developer
Export data from your Database Cloud Service

Resources

Oracle Cloud Habitation Page
To learn more near Oracle Awarding Express, refer to boosted OBEs in the Oracle Learning Library

To help navigate this Oracle past Example, note the following:

Hiding Header Buttons:: Click the Championship to hide the buttons in the header. To show the buttons once again, simply click the Title again.
Topic Listing Push:: A list of all the topics. Click one of the topics to navigate to that department.
Expand/Plummet All Topics:: To evidence/hide all the detail for all the sections. Past default, all topics are collapsed
Show/Hide All Images:: To bear witness/hibernate all the screenshots. By default, all images are displayed.
Print:: To print the content. The content currently displayed or hidden volition be printed.

To navigate to a particular department in this tutorial, select the topic from the list.

danielsvirinarlecou.blogspot.com

Source: https://www.oracle.com/technetwork/tutorials/tutorials-1866309.html