There are different ways to load data to Autonomous Transaction Processing. If you
have small volumes of data to load, you can load data by SQL*Net, but if you
have large volumes of data, the preferred technique is to load from files stored
in a cloud-based object store such as Oracle Object Storage or Amazon AWS S3.
Let's find out how to upload data to Oracle Object Storage and then copy this
staged data into database tables in Autonomous Transaction Processing.
Start with uploading the data. Sign in to Oracle Cloud Interface. Select Object
Storage from the storage menu. Create a bucket in a compartment of Oracle Object
Storage. Click Upload Object to begin selection
of the data files you want to upload to the bucket. Navigate to the location of
the data files on your local computer. Drag and drop each file or click Upload
Object to upload each file. Here, the data files of the Sales History tables from
an Oracle sample schema are uploaded. You can also use cURL commands to upload large
numbers of files.
The data files are uploaded to the bucket. These files, staged to the cloud,
are ready to be copied into the tables of your Autonomous Transaction
Processing database. To load data from an Oracle Cloud
Infrastructure Object Storage object store, you will need to create an Auth
Token for your object store account. The communication between your Autonomous
Transaction Processing database and the object store relies on the auth token
and username password authentication. Click User Settings under your user name.
On the Object Storage console, click Auth Tokens under Resources on the
left of the console. Click Generate Token.
A pop-up dialog appears. Set the auth token by performing the following steps.
In the pop-up dialog, enter a description. Click the Generate Token button.
Copy the generated token to a text file. It will not be shown again. Click Close.
Next, store the credentials of the object store in the target schema of your
Autonomous Transaction Processing database. Open SQL Developer and
connect to your Autonomous Transaction Processing database as your target user.
In a SQL Developer worksheet, use the create_credential procedure of the DBMS_
CLOUD package to store the object store credentials in your target user schema.
Create a credential name. You will reference this credential name in the
copy_data procedure in the next step. Specify the credentials for your Oracle
Cloud Infrastructure Object Storage service, the Object Storage username and
auth token generated in the previous step. After you run the script, your
object store's credentials will be stored in your Autonomous Transaction
Processing user schema. In a SQL Developer worksheet, use the copy_data
procedure of the dbms_cloud package to copy the data staged in your object
store to your Autonomous Transaction Processing user tables. For credential
name, specify the name of the credential you defined. For file_uri_list, specify
the URL that points to the location of the file staged in your object store. The
URL is structured as follows, where you specify the region name, tenant name,
bucket name, and file name. The data has been copied from the object
store to the tables in your Autonomous Transaction Processing database.
All data load operations done using the PL/SQL package dbms_cloud are logged
in two tables. The dba_load_operations table shows all load operations The user_
load_operations table shows the load operations in your schema.
This is a query on the user_load_ operations table. It shows that five rows
were loaded into the CHANNELS table with no failures. The log and bad files are
accessible as tables. For detailed steps on performing the example shown in this
video, go to the Oracle Learning Library and open the tutorial named Loading Your
Data into Autonomous Transaction Processing.
Find out more at cloud.oracle.com/atp