CREATE TABLE T1
(
  COL1  NUMBER,
  COL2  NUMBER
);

------------
CREATE TABLE T2
(
  COL1  NUMBER,
  COL2  NUMBER
);
---------------

insert into t2 values (1,1); 

commit;

--------------


CREATE OR REPLACE procedure P1
is

v_column varchar2(32767);
v_column_cur varchar2(32767);


cursor cur_fact is
select * from T2@prd;
Type  TAB_OIC is table of cur_fact%rowtype index by binary_integer;
t_OIC TAB_OIC;


BEGIN


for x in ( select column_name
from user_tab_columns@prd
where table_name = 'T2'
order by column_id )
loop
v_column := x.column_name || ', ' || v_column;
v_column_cur := 't_oic(i).' || x.column_name || ', ' || 
end loop;
v_column := substr(v_column,1,length(v_column)-2);
v_column_cur := substr(v_column_cur,1,length(v_column_cur)-2);


open cur_fact;
loop
t_oic.DELETE(1,t_oic.COUNT);
FETCH cur_fact bulk collect into t_oic LIMIT 2000;
EXIT WHEN t_oic.COUNT = 0;
for i in 1..t_oic.count loop

v_column := 'insert  into T1 (' || v_column || ')' || ' values (' || v_column_cur || ');';
dbms_output.put_line(v_column);
execute immediate (v_column);

end loop;
commit;
end loop;
commit;
close cur_fact;


End;
/

INSERT INTO T1 SELECT * FROM T2@PRD;

INSERT INTO T1 (col list...) SELECT col list... FROM T2@PRD;

INSERT INTO T1 SELECT * FROM T2@PRD;
or even
INSERT INTO T1 (col list...) SELECT col list... FROM T2@PRD;