使用blob列在Oracle上准备测试数据 [英] Prepare test data on Oracle with blob column

问题描述

最近，我需要针对oracle 10g做一些性能测试，情况是我需要准备尽可能多的具有blob列的记录，并且blob列必须至少包含10kbs的数据.

Recently, I need to do some performance test against oracle 10g, the case is that I need to prepare as many records as possible which has blob columns, and the blob column must at least contain 10kbs of data.

如何自动生成测试数据?说10,000条记录?

How do I automatically generate the test data? Say, 10,000 records?

推荐答案

如果您不关心LOB数据的内容是否相同，则可以使用以下方法(创建一个随机的10000字节lob值):

If you do not care that the content of the LOB data is the same, you can use the following (creating one random 10000 bytes lob value):

declare
   v_clob        clob;
   v_blob        blob;
   v_dest_offset integer := 1;
   v_src_offset  integer := 1;
   v_warn        integer;
   v_ctx         integer := dbms_lob.default_lang_ctx;
begin
   for idx in 1..5
   loop
     v_clob := v_clob || dbms_random.string('x', 2000);
   end loop;
   dbms_lob.createtemporary( v_blob, false );
   dbms_lob.converttoblob(v_blob,
                          v_clob,
                          dbms_lob.lobmaxsize,
                          v_dest_offset,
                          v_src_offset,
                          dbms_lob.default_csid,
                          v_ctx,
                          v_warn);
   insert into blob_test (id, data)
     select rownum, v_blob from dual
     connect by level <= 10000;
end;
/

请参阅此处的示例会话:

See here an example session:

SQL> create table blob_test (id number primary key, data blob);

Table created.

SQL> declare
  2    v_clob        clob;
  3    v_blob        blob;
  4    v_dest_offset integer := 1;
  5    v_src_offset  integer := 1;
  6    v_warn        integer;
  7    v_ctx         integer := dbms_lob.default_lang_ctx;
  8  begin
  9    for idx in 1..5
 10    loop
 11      v_clob := v_clob || dbms_random.string('x', 2000);
 12    end loop;
 13    dbms_lob.createtemporary( v_blob, false );
 14    dbms_lob.converttoblob(v_blob,
 15                           v_clob,
 16                            dbms_lob.lobmaxsize,
 17                           v_dest_offset,
 18                           v_src_offset,
 19                           dbms_lob.default_csid,
 20                           v_ctx,
 21                           v_warn);
 22
 23    insert into blob_test (id, data)
 24    select rownum, v_blob
 25      from dual
 26     connect by level <= 10000;
 27
 28  end;
 29  /

PL/SQL procedure successfully completed.


SQL> select count(*) , max(length(data)) from blob_test;

  COUNT(*) MAX(LENGTH(DATA))
---------- -----------------
     10000             10000

如果要使用其他数据，则可以生成v_lob并将其插入循环中.

If you want to have different data you can generate and insert v_lob in a loop instead.

这篇关于使用blob列在Oracle上准备测试数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！