There is something wrong in my mind about the "REPLACE" mode of Oracle SQL Loader, I have thought that sqlldr will use a merge logic, to replace matched rows if the target table has a primary key. But it's not working this way. When I unload few rows (where dept=10) from "SCOTT.EMP" table with "MODE=REPLACE", and then load it to "TEST.EMP" with existing rows. I found the old rows are deleted.
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'uldrdata.txt' "STR X'0a'"
replace INTO TABLE emp
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(.....)
Actually with this mode, sqlldr do a delete on target first. I found the following SQL in V$SQL.
DELETE /*+NESTED_TABLE_SET_REFS+*/ FROM EMP
Then Oracle found that deleting is not high effective, so introduced the "TRUNCATE" mode into sqlldr.