Oracle – How to Export a Large Number of Files from a Table

bloboracleoracle-11g

I'm using Oracle Database 11g Release 11.1.0.6.0 and have a table recording a very big amount of files (GIF) with IDs (ID). 

  Name                                      Type
  ----------------------------------------- ----------------------------
  ID                                        VARCHAR2(50)
  GIF                                       BLOB

And currently I'm using this piece of code to export the pictures:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

This code works well, BUT the problem is, there're about 100 million rows in the table. On my computer the export speed is about only 7 pictures/second.

So, is there any method that can accelerate the exporting? Thank you!

Best Answer

You can use the ORA_HASH function to split the table data in distinct groups. Look at the modified SELECT-FROM-MY_TABLE-clause. Run this script in three different sqlplus sessions and measure the number of files per seconds that are extracted.

script 0:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=0) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 1:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=1) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 2:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=2) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

If this works you can try to generate and start more scripts.

Related Question