If you ever have to do performance testing with for example backup solutions, you need a lot of data. In this post I want to provide you with a simple PL/SQL script that will generate as much data as you want.

For my own testing I create a schema user with a bigfile tablespace and “unlimited” size and unlimited quotas for the user. I grant the user select on dba_objects and then run the following script as the user.

set serveroutput on
declare
  v_loop_times NUMBER := 15; -- how many times to loop (try 15 first)
  v_table_name VARCHAR2(20) := 'YOUR_TEST_TABLE_NAME';
begin

  execute immediate 'create table '|| v_table_name ||' as (select * from DBA_OBJECTS)';

  for i in 1..v_loop_times loop
    dbms_output.put_line('Round ' || i);
    execute immediate 'insert into '|| v_table_name ||' select * from '|| v_table_name;
    commit;
  end loop;
end;
/

Until next time!