Hi Connor,

Thanks a lot for your quick reply.
If Data volume is in billions, will there be any issue with HWM.

I read this issue in some articles that's why asking just for my knowledge.

And what about SQL*Plus COPY command?

sqlcl's copy command is ideal for things like this too. no need for a database link.

SQL> help copy
COPY
----

Non sqlplus additions are explicit BYTE options (default is CHAR
i.e. varchar2(1000 CHAR)) and LOB support.

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER, LOB and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE|APPEND_BYTE|CREATE_BYTE|REPLACE_BYTE}
destination_table
[(column, column, column, ...)] USING query

where database has the following syntax:
username[/password]@connect_identifier

SQL>

Hi Connor,

Thanks for your reply.
But will there be any problem with High Water Mark if we use APPEND hint?

Is there any steps required if we go for INSERT with APPEND?

Hi Connor,
Thanks for your reply.

APPEND will insert records above High Water Mark, so will there be any issues after inserting huge amount of data using APPEND?

Are there any steps post INSERT with APPEND to avoid any issues?

insert /*+ APPEND */ into local_table select * from table@database_link;

is good, but I have a job that runs from the local database. It does:

1) insert /*+ APPEND */ into local_staging select * from local_source
2) partition exchange local_staging with local_destination
3) calls a procedure on remote database to:
3a) insert /*+ APPEND */ into remote_staging select * from local_source@local_link
3b) partition exchange remote_staging with remote_destination

Of course 3a does not do a direct path since it's distributed (initiated by a call over db link). How can I get around this?

source_table contains potentially one billion rows and my goal is speed.