There are usually 3 ways for running impdp: loading the dump file from the Oracle Directory, Object Storage, or through database links. Let's say you don't permission to use database links for whatever reason.
When you are in ADB world, in an isolated environment, usually we first upload the dump file to the Object Storage and load it using OCI native URIs or Swift URIs. You may also use DBMS_CLOUD.GET_OBJECT to copy the file from the OS to the Oracle Directory. After the file is placed in the directory, we call data pump impdp to load it.
I have a scenario where all I have is user access to an ADB, but there is no database link and no Object Storage. All I have is:
- READ and WRITE in DATA_PUMP_DIR
- CREATE SESSION
- CREATE TABLE
I need to load a huge data pump file into this schema. How do I do that?
Getting Started
So my strategy in this blog post will be to use sqldr to load this datapump file into a BLOB field, and later use DBMS packages to write into the Oracle Directory the contents of this BLOB column:
myfile.dmp -> TABLE (BLOB field) -> DATA_PUMP_DIR directory -> impdp
1. Creating the BLOB table
My table structure will be:
CREATE TABLE lob_tab ( file_name varchar2(1000), blob_content BLOB );
Running it:
$ sqlplus /@hash
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jun 7 16:38:33 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Tue Jun 07 2022 16:32:07 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.1.0
SQL> DROP TABLE lob_tab PURGE;
DROP TABLE lob_tab PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE lob_tab (
2 file_name varchar2(1000),
3 blob_content BLOB
4 );
Table created.
SQL>
2. Preparing sqlldr control file
My sqlldr control file will only have:
LOAD DATA
INFILE 'lob_test_data.txt'
append
INTO TABLE lob_tab
FIELDS TERMINATED BY ','
(file_name CHAR(100),
blob_content LOBFILE(file_name) TERMINATED BY EOF)
So I'm basically inserting into the table the file name and its contents.
Generating the control file:
$ cat > lob_test.ctl <<'EOF' > LOAD DATA > INFILE 'lob_test_data.txt' > append > INTO TABLE lob_tab > FIELDS TERMINATED BY ',' > (file_name CHAR(100), > blob_content LOBFILE(file_name) TERMINATED BY EOF) > EOF
3. Loading my dump
In this example, I will load 2 dump files in the ADB:
$ echo 'mydump_meta_backup_20220606_175153.dmp' > lob_test_data.txt $ sqlldr /@hash control=lob_test.ctl log=lob_test.log bad=lob_test.bad SQL*Loader: Release 21.0.0.0.0 - Production on Tue Jun 7 16:42:18 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 1 Table LOB_TAB: 1 Row successfully loaded. Check the log file: lob_test.log for more information about the load. $ echo 'mydump_data_backup_20220606_175153.dmp' > lob_test_data.txt $ sqlldr /@hash control=lob_test.ctl log=lob_test.log bad=lob_test.bad SQL*Loader: Release 21.0.0.0.0 - Production on Tue Jun 7 16:42:32 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 1 Table LOB_TAB: 1 Row successfully loaded. Check the log file: lob_test.log for more information about the load. $
4. Checking rows in the database
$ sqlplus /@hash <<< 'select file_name from lob_tab;' SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jun 7 16:44:23 2022 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Last Successful login time: Tue Jun 07 2022 16:42:33 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.1.0 SQL> FILE_NAME -------------------------------------------------------------------------------- mydump_meta_backup_20220606_175153.dmp mydump_data_backup_20220606_175153.dmp SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.1.0 $
5. Copying file from the table into the Oracle Directory
Now I will use DBMS_LOB and UTL_FILE packages to read and write this LOB into the directory. The code I will use is:
DECLARE
V_DIRECTORY VARCHAR2(30) := 'DATA_PUMP_DIR';
PROCEDURE RETRIEVE_LOB_TO_FILE (
TEMP_BLOB IN BLOB,
FILE_PATH IN VARCHAR2,
FILE_NAME IN VARCHAR2
) IS
DATA_BUFFER RAW(32767);
POSITION INTEGER := 1;
FILEHANDLE UTL_FILE.FILE_TYPE;
ERROR_NUMBER NUMBER;
ERROR_MESSAGE VARCHAR2(100);
BLOB_LENGTH INTEGER;
CHUNK_SIZE BINARY_INTEGER := 32767;
BEGIN
BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
FILEHANDLE := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
WHILE POSITION < BLOB_LENGTH LOOP
DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
POSITION := POSITION + CHUNK_SIZE;
DATA_BUFFER := NULL;
END LOOP;
UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
WHEN OTHERS THEN
BEGIN
ERROR_NUMBER := SQLCODE;
ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
UTL_FILE.FCLOSE_ALL;
END;
END;
BEGIN
FOR I IN (
SELECT *
FROM LOB_TAB
) LOOP
RETRIEVE_LOB_TO_FILE(I.BLOB_CONTENT, V_DIRECTORY, I.FILE_NAME);
END LOOP;
END;
/
Calling it:
SQL> @copy_lob.sql
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') where object_name like 'mydump%.dmp';
OBJECT_NAME BYTES CHECKSUM CREATED LAST_MODIFIED
---------------------------------------- ---------- ----------- -------------------------------------- ------------------------------------
mydump_meta_backup_20220606_175153.dmp 933888 07-JUN-22 04.50.59.000000 PM +00:00 07-JUN-22 04.51.02.000000 PM +00:00
mydump_data_backup_20220606_175153.dmp 577536 07-JUN-22 04.51.03.000000 PM +00:00 07-JUN-22 04.51.04.000000 PM +00:00
SQL>
6. Running Datapump
Now that I have my dump files placed, I can finally call impdp:
impdp /@hash \ directory=data_pump_dir \ dumpfile=mydump_meta_backup_20220606_175153.dmp \ logfile=impdp_mydump_meta_backup_20220606_175153.log \ logtime=all
7. Clean the Oracle Directory folder
Finally, after everything is completed successfully, I can clean out the used objects and files:
DROP TABLE lob_tab PURGE;
BEGIN
FOR I IN (SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') where object_name like '%mydump%')
LOOP
DBMS_CLOUD.DELETE_FILE('DATA_PUMP_DIR',I.OBJECT_NAME);
END LOOP;
END;
/
Conclusion
This is not the fastest way, but a good workaround when you lack some access.
Have you enjoyed? Please leave a comment or give a 👍!
