Deletes duplicate inserted Data Based on ora_rowscn

Before leaving work today, I suddenly received a call from the Bureau asking for a detailed call record for June October.

Then, the entire table is started and the extracted records are inserted into the detail table. Now, we can delete tens of thousands of Repeated Records in the 100 billion large table.

-- The following is a solution:

-- 1. Find the submitted value ora_rowscn

-- Select scn_to_timestamp (ora_rowscn) from dual view submission time

Select/* + parallel (12) */dt. ora_rowscn, count (*) from table_name (privacy-related) dt, table_ B (privacy-related) cp
Where dt. starttime> = to_date ('2017-10-05 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And dt. starttime <to_date ('2017-10-06 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And dt. called in ('20140901', 'xxxxx ')
And substr (dt. caller, 1, 3) in ('..','..','....')
And dt. xxx = cp. xx
And cp. ldcflag = 1
-- And dt. ora_rowscn = 31902278734
Group by dt. ora_rowscn
Order by 2 desc
;

--
-- Verification time
Select scn_to_timestamp (31903242189) from dual

 
-- Number of verification entries
-- Select 10910 + 1841 from dual

-- Create a table
Create table rowid_4
Select rowid rowids from table_name (privacy-related) dt
Where dt. starttime> = to_date ('2017-10-04 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And dt. starttime <to_date ('2017-10-05 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And dt. ora_rowscn in (33661488022,33665491128)

-- Number of verification entries
Select count (*) from rowid_4;

-- Delete duplicate data based on rowid
Delete/* + parallel (8) nologging use_hash (dt ssd) */from table_name (privacy-related) dt
Where dt. rowid in (select rowids from rowid_4 ssd)