As you probably already know, Oracle APEX enables you to build applications with minimal coding. APEX is already available in all Oracle Autonomous Database workload types: transaction processing, data warehouse, and JSON. You get a fully-managed database where you can even more focus on your application development.
Additionally, Oracle provides an Autonomous Database specifically for APEX applications for a much lower cost.
However, the lower cost comes with some limitations. One of the most important limitations to be highlighted here is the missing support for Oracle Database clients, this means, SQL*Net connectivity is not available. Hence, you are not able to connect to the database via Data Pump impdp client either! So how do you get your existing database schemas into the newly created database???
In this blog post, we are going to use the DBMS_DATAPUMP PL/SQL API to import data from Data Pump dump files into Oracle Autonomous Database – APEX Service.
Additionally, you’ll see the use cases when and how to use the following DBMS_CLOUD functions and procedures:
- Oracle Autonomous Database – Shared Infrastructure APEX Service.
- Data Pump dump file (sampleuser.dmp) already uploaded to Object Storage, which contains the schema (SAMPLEUSER) that needs to be imported into the Autonomous Database – APEX Service.
- Object Storage namespace: oci_core_emea
- Object Storage bucket name: dumps
Import Data using the DBMS_DATAPUMP PL/SQL API
Step 1: Connect to your Autonomous Database using Database Actions in the Cloud Console
From your Autonomous Database details page, select the Tools tab, then Open Database Actions. Provide your username (ADMIN) and password. Finally, click on SQL to open what was initially called SQL Developer Web.
Step 2: Allow your Autonomous Database to Access Object Storage
Follow step 3 in this blog to create your credentials that enable the Autonomous Database to access the dump file in the Object Storage. To verify the access, list the files in your Object Storage bucket using the LIST_OBJECTS function of the DBMS_CLOUD PL/SQL package:
select object_name from DBMS_CLOUD.LIST_OBJECTS('OBJECT_STORE_CRED', 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/');
Step 3: Import your Data into the Autonomous Database – APEX Service
Use the DBMS_DATAPUMP PL/SQL API to import your schemas into the Autonomous Database:
DECLARE -- replace with your values exported_schema VARCHAR2(64) := 'SAMPLEUSER'; import_schema VARCHAR2(64) := 'SAMPLEUSER'; -- in case you want to remap schema data_pump_dir VARCHAR2(64) := 'DATA_PUMP_DIR'; dump_file_name VARCHAR2(256) := 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/sampleuser.dmp'; credential_name VARCHAR2(64) := 'OBJECT_STORE_CRED'; parallel NUMBER := 4; job_handle NUMBER; job_name VARCHAR2(64); job_status VARCHAR2(128); output_message VARCHAR2(1024); BEGIN job_name := dbms_scheduler.generate_job_name('import_'); job_handle := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => job_name); dbms_datapump.add_file(handle => job_handle, filename => dump_file_name, directory => credential_name, filetype => dbms_datapump.ku$_file_type_uridump_file); dbms_datapump.add_file(handle => job_handle, filename => import_schema || '_import.log', directory => data_pump_dir, filetype => 3); dbms_datapump.metadata_remap(job_handle, 'REMAP_SCHEMA', exported_schema, import_schema); dbms_datapump.metadata_filter(handle => job_handle, name => 'SCHEMA_EXPR', value => 'IN(''' || exported_schema || ''')'); dbms_datapump.set_parallel(handle => job_handle, degree => parallel); dbms_datapump.start_job(handle => job_handle, skip_current => 0, abort_step => 0); dbms_datapump.wait_for_job(handle => job_handle, job_state => job_status); output_message := 'Data Pump Import Execution: ''' || job_status || ''''; dbms_output.put_line(output_message); END; /
After successful execution, you’ll get an output similar to the following:
Data Pump Import Execution: 'COMPLETED' PL/SQL procedure successfully completed. Elapsed: 00:00:11.870
Step 4: Check the Data Pump Import Log File
List the files in the DATA_PUMP_DIR directory using the LIST_FILES function:
select * from DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') order by last_modified desc;
Now you will see that the SAMPLEUSER_import.log file was created. As we don’t have access to the file system, we will need to copy the log file to Object Storage first using the PUT_OBJECT procedure, and then download it from there:
BEGIN DBMS_CLOUD.PUT_OBJECT( 'OBJECT_STORE_CRED', 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oci_core_emea/b/dumps/o/SAMPLEUSER_import.log', 'DATA_PUMP_DIR', 'SAMPLEUSER_import.log'); END; / PL/SQL procedure successfully completed.
Having a look at the log file:
Master table "ADMIN"."IMPORT_180" successfully loaded/unloaded Starting "ADMIN"."IMPORT_180": Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SAMPLEUSER"."SAMPLETABLE" 5.062 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "ADMIN"."IMPORT_180" successfully completed at Mon Aug 23 14:51:24 2021 elapsed 0 00:00:05
Step 5: Clean Up
Files in the DATA_PUMP_DIR directory counts towards your Autonomous Database storage usage. Even though the log file is usually not as big, clean up you files after the import is completed. Delete the log file from the DATA_PUMP_DIR directory using the DELETE_FILE procedure:
BEGIN DBMS_CLOUD.DELETE_FILE('DATA_PUMP_DIR', 'SAMPLEUSER_import.log'); END; / PL/SQL procedure successfully completed.
You also could delete the dump file from the Object Storage bucket as well using the DELETE_OBJECT procedure:
BEGIN DBMS_CLOUD.DELETE_OBJECT( credential_name => 'OBJECT_STORE_CRED', object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/oci_core_emea/dumps/sampleuser.dmp'); / PL/SQL procedure successfully completed.
Oracle Autonomous Database – APEX Service provides a fully managed database for your low-code application development at a very lower cost than the standard Autonomous Database service. As the APEX service does not provide SQL*Net connectivity via database clients, it is not possible to import your existing database schemas as usual by using the Data Pump impdp client. Instead, you connect to the SQL Action in the Cloud Console and use the DBMS_DATAPUMP PL/SQL API.
Additionally, we have seen when and how to use the LIST_OBJECTS, LIST_FILES, PUT_OBJECT, DELETE_OBJECT, and DELETE_FILE functions and procedures of the DBMS_CLOUD package.
By the way, Autonomous APEX is included in the Always Free resources. So just try it out for free, forever!
- Use your Web Browser to import Data Pump dump files into Oracle Autonomous Database
- Part 1/10: Why to Migrate your Oracle Database to Oracle Cloud?
- Why do I get “ORA-12899 value too large for column” when migrating to Autonomous Database?