- \n
- READ and WRITE in DATA_PUMP_DIR<\/li>\n
- CREATE SESSION<\/li>\n
- CREATE TABLE<\/li>\n<\/ul>\n
I need to load a huge data pump file into this schema. How do I do that?<\/p>\n
Getting Started<\/h3>\n
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:<\/p>\n
myfile.dmp -> TABLE (BLOB field) -> DATA_PUMP_DIR directory -> impdp<\/span><\/strong><\/p>\n
1. Creating the BLOB table<\/h4>\n
My table structure will be:<\/p>\n
CREATE TABLE lob_tab (\r\n file_name varchar2(1000),\r\n blob_content BLOB\r\n);<\/pre>\n
Running it:<\/p>\n
$ sqlplus \/@hash\r\n\r\nSQL*Plus: Release 21.0.0.0.0 - Production on Tue Jun 7 16:38:33 2022\r\nVersion 21.3.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle. All rights reserved.\r\n\r\nLast Successful login time: Tue Jun 07 2022 16:32:07 +00:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.15.0.1.0\r\n\r\nSQL> DROP TABLE lob_tab PURGE;\r\nDROP TABLE lob_tab PURGE\r\n *\r\nERROR at line 1:\r\nORA-00942: table or view does not exist\r\n\r\n\r\nSQL> CREATE TABLE lob_tab (\r\n 2 file_name varchar2(1000),\r\n 3 blob_content BLOB\r\n 4 );\r\n\r\nTable created.\r\n\r\nSQL><\/pre>\n
2. Preparing sqlldr control file<\/h4>\n
My sqlldr control file will only have:<\/p>\n
LOAD DATA \r\nINFILE 'lob_test_data.txt'\r\nappend\r\n INTO TABLE lob_tab\r\n FIELDS TERMINATED BY ','\r\n (file_name CHAR(100),\r\n blob_content LOBFILE(file_name) TERMINATED BY EOF)<\/pre>\n
So I’m basically inserting into the table the file name and its contents.<\/p>\n
Generating the control file:<\/p>\n
$ cat > lob_test.ctl <<'EOF'\r\n> LOAD DATA\r\n> INFILE 'lob_test_data.txt'\r\n> append\r\n> INTO TABLE lob_tab\r\n> FIELDS TERMINATED BY ','\r\n> (file_name CHAR(100),\r\n> blob_content LOBFILE(file_name) TERMINATED BY EOF)\r\n> EOF<\/pre>\n
3. Loading my dump<\/h4>\n
In this example, I will load 2 dump files in the ADB:<\/p>\n
$ echo 'mydump_meta_backup_20220606_175153.dmp' > lob_test_data.txt\r\n$ sqlldr \/@hash control=lob_test.ctl log=lob_test.log bad=lob_test.bad\r\n\r\nSQL*Loader: Release 21.0.0.0.0 - Production on Tue Jun 7 16:42:18 2022\r\nVersion 21.3.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nPath used: Conventional\r\nCommit point reached - logical record count 1\r\n\r\nTable LOB_TAB:\r\n 1 Row successfully loaded.\r\n\r\nCheck the log file:\r\n lob_test.log\r\nfor more information about the load.\r\n\r\n\r\n$ echo 'mydump_data_backup_20220606_175153.dmp' > lob_test_data.txt\r\n$ sqlldr \/@hash control=lob_test.ctl log=lob_test.log bad=lob_test.bad\r\n\r\nSQL*Loader: Release 21.0.0.0.0 - Production on Tue Jun 7 16:42:32 2022\r\nVersion 21.3.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nPath used: Conventional\r\nCommit point reached - logical record count 1\r\n\r\nTable LOB_TAB:\r\n 1 Row successfully loaded.\r\n\r\nCheck the log file:\r\n lob_test.log\r\nfor more information about the load.\r\n$<\/pre>\n
4. Checking rows in the database<\/h4>\n
$ sqlplus \/@hash <<< 'select file_name from lob_tab;'\r\n\r\nSQL*Plus: Release 21.0.0.0.0 - Production on Tue Jun 7 16:44:23 2022\r\nVersion 21.3.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle. All rights reserved.\r\n\r\nLast Successful login time: Tue Jun 07 2022 16:42:33 +00:00\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.15.0.1.0\r\n\r\nSQL>\r\nFILE_NAME\r\n--------------------------------------------------------------------------------\r\nmydump_meta_backup_20220606_175153.dmp\r\nmydump_data_backup_20220606_175153.dmp\r\n\r\nSQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.15.0.1.0\r\n$<\/pre>\n
5. Copying file from the table into the Oracle Directory<\/h4>\n
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:<\/p>\n
DECLARE\r\n V_DIRECTORY VARCHAR2(30) := 'DATA_PUMP_DIR';\r\n\r\n PROCEDURE RETRIEVE_LOB_TO_FILE (\r\n TEMP_BLOB IN BLOB,\r\n FILE_PATH IN VARCHAR2,\r\n FILE_NAME IN VARCHAR2\r\n ) IS\r\n\r\n DATA_BUFFER RAW(32767);\r\n POSITION INTEGER := 1;\r\n FILEHANDLE UTL_FILE.FILE_TYPE;\r\n ERROR_NUMBER NUMBER;\r\n ERROR_MESSAGE VARCHAR2(100);\r\n BLOB_LENGTH INTEGER;\r\n CHUNK_SIZE BINARY_INTEGER := 32767;\r\n BEGIN\r\n BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);\r\n FILEHANDLE := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);\r\n WHILE POSITION < BLOB_LENGTH LOOP\r\n DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);\r\n UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);\r\n POSITION := POSITION + CHUNK_SIZE;\r\n DATA_BUFFER := NULL;\r\n END LOOP;\r\n\r\n UTL_FILE.FCLOSE(FILEHANDLE);\r\n EXCEPTION\r\n WHEN OTHERS THEN\r\n BEGIN\r\n ERROR_NUMBER := SQLCODE;\r\n ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);\r\n DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);\r\n DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);\r\n UTL_FILE.FCLOSE_ALL;\r\n END;\r\n END;\r\n\r\nBEGIN\r\n FOR I IN (\r\n SELECT *\r\n FROM LOB_TAB\r\n ) LOOP\r\n RETRIEVE_LOB_TO_FILE(I.BLOB_CONTENT, V_DIRECTORY, I.FILE_NAME);\r\n END LOOP;\r\nEND;\r\n\/<\/pre>\nCalling it:<\/p>\n
SQL> @copy_lob.sql\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') where object_name like 'mydump%.dmp';\r\n\r\nOBJECT_NAME BYTES CHECKSUM CREATED LAST_MODIFIED\r\n---------------------------------------- ---------- ----------- -------------------------------------- ------------------------------------\r\nmydump_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\r\nmydump_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\r\n\r\nSQL><\/pre>\n6. Running Datapump<\/h4>\n
Now that I have my dump files placed, I can finally call impdp:<\/p>\n
\nimpdp \/@hash \\\r\ndirectory=data_pump_dir \\\r\ndumpfile=mydump_meta_backup_20220606_175153.dmp \\\r\nlogfile=impdp_mydump_meta_backup_20220606_175153.log \\\r\nlogtime=all<\/pre>\n
7. Clean the Oracle Directory folder<\/h4>\n
Finally, after everything is completed successfully, I can clean out the used objects and files:<\/p>\n
DROP TABLE lob_tab PURGE;\r\n\r\nBEGIN\r\n FOR I IN (SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR') where object_name like '%mydump%')\r\n LOOP\r\n DBMS_CLOUD.DELETE_FILE('DATA_PUMP_DIR',I.OBJECT_NAME);\r\n END LOOP;\r\nEND;\r\n\/<\/pre>\nConclusion<\/h3>\n
This is not the fastest way, but a good workaround when you lack some access.<\/p>\nHave you enjoyed? Please leave a comment or give a \ud83d\udc4d!<\/b>\n<\/div>\n
