Well...we need to determine *why* its taking a long time. It could be:
- time to read the source data
- time to transfer it across dblink
- time to write it
- something else
Let's try work out that first.
So lets assume the CTAS is being done as user X, and is being done as:
create table T as select * from T@source_db
and the 'source_db' dblink connects as user Y.
So we want to capture details on both source and target.
On the source, we want to turn on a trace when the db link connects as Y from the target, so you can do:
create or replace
trigger TRACE_Y
after logon on Y.schema
declare
l_details varchar2(200);
begin
select ...
into l_details
from v$session
where sid = sys_context('USERENV','SID');
if l_details like '...' then
execute immediate 'alter session set tracefile_identifier = DBLINK_TRACE';
dbms_monitor.session_trace_enable(waits=>true);
end if;
exception
when others then null;
end;
/
where you can do some experiment with the 'details' above to ensure you only activate the trace where apropriate. (Obviously if 'Y' is specific to the db link only and not other connections you wont this additional check).
Then on the target you do:
dbms_monitor.session_trace_enable(waits=>true);
create table T as select * from T@source_db where rownum <= 1000000
(Choose a value for rownum that will give you a reasonable run time, eg, 30mins, so we have a good idea of where the time is being spent)
And its a case of looking at the trace files and working from there.