I need to extract LOB segments from the DB before I delete them in an effort to free up datafile space. We do not have an archive system, so I've gone ahead to make a real document backup of the files before I do the delete and data file shrink.
To extract en masse - see the code below. For this to work, I need a table holding LOB segments (<TABLE>) with a filename column and a writable data directory <DB_DIRECTORY>.
I adjust 'rownum < 11' to set the criteria for the data I wish to extract. For my objective, I will be using a data range.
See the PL/SQL block below.
BEGIN
/* Load the lobs */
FOR r_blob IN (SELECT report_output, filename FROM <TABLE> WHERE created_date < To_Date('2015-06-01','YYYY-MM-DD'))
LOOP
DECLARE
v_file UTL_FILE.FILE_TYPE;
v_buffer RAW(32767);
v_amount BINARY_INTEGER := 32767;
v_pos NUMBER := 1;
v_blob_len NUMBER;
BEGIN
/* Reset the blob cursor */
v_pos := 1;
/* Grab the lob size */
v_blob_len := DBMS_LOB.getlength(r_blob.report_output);
/* Open the file we want to hold the blob */
v_file := UTL_FILE.fopen(<DB_DIRECTORY>, r_blob.filename,'WB', 32767);
/* Write blocks of 32767 bytes at a time */
WHILE v_pos < v_blob_len LOOP
/* Read the contents of the lob from the db in 32767 chunk */
DBMS_LOB.READ(r_blob.report_output, v_amount, v_pos, v_buffer);
/* Write the buffer back out to file */
UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
/* Increment to load the next chunk */
v_pos := v_pos + v_amount;
END LOOP;
/* Close the file */
UTL_FILE.fclose(v_file);
END;
END LOOP;
END;
/
No comments:
Post a Comment