create table table3 as
select case mod(level,3) when 0 then 'Петя' else 'Вася' end name
from dual connect by level < 15000
;

Table created.

create table table1 as select * from table3 where 1=0;

Table created.

create table table2 as select * from table3 where 1=0;

Table created.

select count(*) from table3;

COUNT(*)
14999

DECLARE
  cursor c3 is select * from table3;
  type t_buffer is table of c3%rowtype index by pls_integer;
  v_buffer t_buffer;
  type t_ind_arr is table of pls_integer index by pls_integer;
  v_idx_t1 t_ind_arr;
  v_idx_t2 t_ind_arr;
  c_blocksize constant integer := 100;
    V_T1_CNT NUMBER := 0;
    V_T2_CNT NUMBER := 0;
BEGIN
  open c3; -- Долгоиграющий запрос
  loop
    v_idx_t1.delete;
    v_idx_t2.delete;

    -- Дергаем c_blocksize записей в буфер
    fetch c3 bulk collect into v_buffer limit c_blocksize;

    -- Делим на Петь и прочих
    for i in 1..v_buffer.count loop
      case v_buffer(i).name
        when 'Петя' then v_idx_t1(v_idx_t1.count+1) := i;
        else v_idx_t2(v_idx_t2.count+1) := i;
      end case;
    end loop;
   
    -- Загоняем данные по предписанию
    forall i in values of v_idx_t1
      insert into table1 values v_buffer(i);
    v_t1_cnt := v_t1_cnt + v_idx_t1.count;

    forall i in values of v_idx_t2
      insert into table2 values v_buffer(i);
    v_t2_cnt := v_t2_cnt + v_idx_t2.count;

    exit when v_buffer.count < c_blocksize;
  end loop;
  close c3;

    DBMS_OUTPUT.PUT_LINE(V_T1_CNT || ' row(s) inserted into TABLE1');
    DBMS_OUTPUT.PUT_LINE(V_T2_CNT || ' row(s) inserted into TABLE2');
END;
/

4999 row(s) inserted into TABLE1
10000 row(s) inserted into TABLE2

select 'Table1', count(*) from table1
union all select 'Table2', count(*) from table2;

'TABLE1'	COUNT(*)
Table1	4999
Table2	10000

forall save exceptions