SQL & PL/SQL

Oracle 9i
===========

Hi,

We are facing an issue where we have to copy any given table to an archive database (and then delete the source table). Due to undo space issues we have to commit frequently. The transportation is done through a dynamic SQL statement. The current logic inserts each row and then deletes the same row within a cursor loop; it commits at every predefined interval of say 10000 record. This logic errors out with an 02067 error which I assume is due to a large number of transactions over a distributed environment.

Can we do this -

INSERT INTO target SELECT * FROM src WHERE rownum <10001
ORDER BY ROWID;
DELETE FROM src WHERE ROWNUM <10001 ORDER BY ROWID;

Will this delete the same records that we have inserted? This way we can break the table into smaller chunks and reduce the number of transactions.

If anyone has a better idea (like how to tackle the 02067 error itself) then please let me know.

Thanks a lot.