You have created an Autonomous Data Warehouse (ADW) service in your company's Oracle Cloud Infrastructure (OCI) tenancy and you now have to load historical data into it.
You have already extracted this historical data from multiple data marts and data warehouses.
This data is stored in multiple CSV text files and these files are ranging in size from 25 MB to 20 GB.
Which is the most efficient and error tolerant method for loading data into ADW? (Choose the best answer.)
Click on the arrows to vote for the correct answer
A. B. C. D. A. F. A. H. .D.
The most efficient and error-tolerant method for loading historical data into an Autonomous Data Warehouse (ADW) service in Oracle Cloud Infrastructure (OCI) tenancy is option H:
H. CREATE_CREDENTIAL, using OCI CLI upload the CSV files to an OCI object storage bucket, create the tables in the ADW database and then execute DBMS_CLOU. COPY_DATA for each CSV file to copy the contents into the corresponding ADW database table.
Here is a detailed explanation of this method:
Step 1: Create Object Storage Credential First, you need to create an object storage credential using the CREATE_CREDENTIAL procedure. This credential will allow you to access the OCI Object Storage service where you will upload your CSV files. You can also create an auth token and use it to create the object storage credential by executing the DBMS_CLOU package.
Step 2: Upload CSV Files to Object Storage Using OCI CLI, upload the CSV files to an OCI object storage bucket. OCI CLI is a command-line interface that enables you to interact with the OCI services from your local computer. Uploading CSV files to object storage will make it easier to access them from the ADW service.
Step 3: Create Tables in ADW Database Create the tables in the ADW database where you want to load the data. You can use SQL Developer or any other tool to create tables. Make sure that the column names and data types in the table match the column names and data types in the CSV files.
Step 4: Load CSV Files into ADW Database Use the DBMS_CLOU package to copy the contents of the CSV files into the corresponding ADW database table. This can be done using the COPY_DATA procedure. COPY_DATA copies data from an object store into an ADW table in parallel, which can be much faster than loading data using SQL*Loader.
This method is efficient because it leverages OCI object storage and the parallel processing capabilities of ADW. It is also error-tolerant because the COPY_DATA procedure can handle errors such as invalid data and duplicates, and can automatically skip over them without failing the entire load operation.
In contrast, the other options are not as efficient or error-tolerant. Option A and D require the use of SQL*Loader, which can be slow and error-prone. Option B and F require the use of Data Pump Import, which can also be slow and may require additional configuration. Option A and C do not take advantage of OCI object storage and may be slower and less efficient.