oracle lob usage

to bring back size of all lobs used in the defined schema :

SELECT   TABLE_NAME, SUM (tab_size) "Table(MB)", SUM(Blocks_Used) "Blocks Used",
         SUM(Unused_Blocks) "Unused Blocks",   SUM(num_rows) "Num Rows", SUM (index_size) "Index(MB)",
         SUM (lobindex_size) "LobIndex(MB)",
         SUM (lobseg_size) "LobSegment(MB)", SUM (CHUNK / 1024) "Chunk (k)",
         SUM (tab_size + lobindex_size + index_size + lobseg_size)
                                                                  "Total(MB)"
    FROM (SELECT TABLE_NAME, BYTES / 1024 / 1024 tab_size, t.blocks Blocks_Used, t.empty_blocks Unused_Blocks,
         t.num_rows num_rows, 0 index_size,
                 0 lobindex_size, 0 lobseg_size, 0 CHUNK
            FROM dba_segments s, dba_tables t
           WHERE s.owner = UPPER ('AESUSER')
             AND t.owner = UPPER ('AESUSER')
             AND s.segment_name = t.TABLE_NAME
             AND TABLE_NAME NOT LIKE 'DR$%'
          UNION
          SELECT   TABLE_NAME, 0 tab_size,0 Blocks_Used, 0 Unused_Blocks,0 num_rows,
                   SUM (BYTES) / 1024 / 1024 index_size, 0 lobindex_size,
                   0 lobseg_size, 0 CHUNK
              FROM dba_segments s, dba_indexes i
             WHERE s.owner = UPPER ('AESUSER')
               AND i.owner = UPPER ('AESUSER')
               AND s.segment_name = i.index_name
               AND segment_type = 'INDEX'
               AND TABLE_NAME NOT LIKE 'DR$%'
          GROUP BY TABLE_NAME
          UNION
          SELECT   TABLE_NAME, 0 tab_size, 0 Blocks_Used, 0 Unused_Blocks,0 num_rows,0 index_size,
                   SUM (BYTES) / 1024 / 1024 lobindex_size, 0 lobseg_size,
                   0 CHUNK
              FROM dba_segments s, dba_indexes i
             WHERE s.owner = UPPER ('AESUSER')
               AND i.owner = UPPER ('AESUSER')
               AND s.segment_name = i.index_name
               AND TABLE_NAME NOT LIKE 'DR$%'
               AND segment_type = 'LOBINDEX'
          GROUP BY TABLE_NAME
          UNION
          SELECT   TABLE_NAME, 0 tab_size,0 Blocks_Used, 0 Unused_Blocks, 0 num_rows,0 index_size,
                   0 lobindex_size,
                   SUM (BYTES) / 1024 / 1024 lobseg_size, CHUNK
                                  FROM dba_segments s, dba_lobs l
             WHERE s.owner = UPPER ('AESUSER')
               AND s.segment_name = l.segment_name
               AND TABLE_NAME NOT LIKE 'DR$%'
               AND segment_type = 'LOBSEGMENT'
          -- and l.table_name=upper('PRITEMDATA')
          GROUP BY TABLE_NAME, CHUNK )
          WHERE TABLE_NAME  IN (SELECT DISTINCT TABLE_NAME FROM all_tab_columns WHERE data_type LIKE'%LOB'
                                AND owner='AESUSER')
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME

 

Scripts to test lob usage on Oracle

Read Blob from database and write to a local file

i needed a method to test the throughput of a system when working with blob data.    A lot of finger pointing occurred at the application middleware, but i was concerned the underlying database, network and/or SAN architecture was at fault.  Hence writing a simple db resident script to do the work of the middleware whose main content fields have Blob/clob definitions.

Steps

1. Define the Stored Procedures
2. Create a table
3. Connect a client – if applicable, run this locally on the db server to remove any client network latency.

 

create stored procedure

CREATE OR REPLACE PROCEDURE kate.WriteBLOBToFILE (itemId IN NUMBER,v_dir varchar2,runno IN number) IS
  v_blob         BLOB;
  blob_length    INTEGER;
  out_file       UTL_FILE.FILE_TYPE;
  v_buffer       RAW(32767);
  chunk_size     BINARY_INTEGER := 32767;
  blob_position  INTEGER := 1;
BEGIN
  -- Retrieve the BLOB for reading
  --SELECT item_data INTO v_blob FROM pritemdata WHERE data_id = (select ContId;
    SELECT  item_data INTO v_blob FROM pritemdata WHERE data_id in (select data_id from prdatareference where item_id=itemId and data_type=1);

  -- Retrieve the SIZE of the BLOB
  blob_length:=DBMS_LOB.GETLENGTH(v_blob);

  -- Open a handle to the location to write the BLOB to file
  out_file := UTL_FILE.FOPEN (v_dir,itemId||'_'||runno , 'wb', chunk_size);

  -- Write the BLOB to file in chunks
  WHILE blob_position <= blob_length LOOP
    IF blob_position + chunk_size - 1 > blob_length THEN
      chunk_size := blob_length - blob_position + 1;
    END IF;
    DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
    UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
    blob_position := blob_position + chunk_size;
  END LOOP;

  -- Close the file handle
  UTL_FILE.FCLOSE (out_file);
END;

Write lob into database

1. Create table

CREATE TABLE BLOB_DATA
(
  ID         NUMBER,
  BLOB_DATA  BLOB,
  PRTIME     TIMESTAMP(6)  DEFAULT SYSTIMESTAMP
)

2. Create Stored Procedure

requirements – grant execute on dbms_lock;

CREATE OR REPLACE PROCEDURE AESUSER.insertBLOB (src_dir VARCHAR2,src_filename IN VARCHAR2,runno IN NUMBER,sleepinterval IN NUMBER) IS

--exec insertBLOB3('DUMPDIR','test.jpg',5,1);

      bfile_offset NUMBER ;
      blob_offset NUMBER;
    v_file_loc       BFILE;
    v_srcfile    BLOB;
    v_srcfile_size   INTEGER;
     lob_fileexists       BOOLEAN := FALSE;

    BEGIN
 DBMS_OUTPUT.ENABLE(100000);
    v_file_loc := BFILENAME(src_dir,src_filename);
     lob_fileexists := DBMS_LOB.FILEEXISTS(v_file_loc) = 1;

      IF  lob_fileexists  THEN
          v_srcfile_size := DBMS_LOB.GETLENGTH(v_file_loc);
            DBMS_OUTPUT.PUT_LINE(src_dir|| '\' ||src_filename);
            DBMS_OUTPUT.PUT_LINE('FILE SIZE: ' ||  v_srcfile_size);
            DBMS_OUTPUT.PUT_LINE('Repetitions: ' || runno);
            DBMS_OUTPUT.PUT_LINE('Sleep INTERVAL: ' || sleepinterval);

                  for i in 1..runno loop
                    bfile_offset  :=1;
                    blob_offset  :=1;
                         INSERT INTO blob_data VALUES (i,EMPTY_BLOB(),systimestamp) returning blob_data into v_srcfile;
                         dbms_lob.fileopen(v_file_loc, dbms_lob.file_readonly);
                        dbms_lob.OPEN( v_srcfile, dbms_lob.lob_readwrite);
                       DBMS_LOB.LOADBLOBFROMFILE (
                               dest_lob   => v_srcfile,
                               src_bfile  => v_file_loc,
                               amount       =>  dbms_lob.lobmaxsize,
                               dest_offset => bfile_offset,
                               src_offset   => blob_offset);
                         dbms_lob.close(v_srcfile);
                         dbms_lob.fileclose(v_file_loc);
                         dbms_lock.sleep(sleepinterval);
                           commit;
                            end loop;
              ELSE
                DBMS_OUTPUT.PUT_LINE('ERROR.  Supplied FILE Name  '||src_dir|| '\' ||src_filename|| ' Does NOT exist');

         END IF;
         EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error:' || SQLERRM);
        dbms_lob.close(v_srcfile);
        dbms_lob.fileclose(v_file_loc);
   END;
/

3. Run Script.

sql> insertblob [oracle_directory],[filename],reptitions,interval);

• oracle_directory – name of directory within the database
• Filename – name of test file to import
• repetitions – qty of times to repeat test
• interval – sleep time between each repetition

remember to set serveroutput on if you wish a report at run end.

SQL> SET serveroutput ON

Success

SQL> EXEC insertBLOB('DUMPDIR','test.jpg',1,0);
DUMPDIR\test.jpg
FILE SIZE: 4637515
Repetitions: 1
Sleep INTERVAL: 0

failure

SQL> EXEC insertBLOB('DUMPDIR','testxxxxxx.jpg',1,0);
ERROR.  Supplied FILE DUMPDIR\testxxxxxx.jpg Does NOT existRead Lob FROM DATABASE