Oracle High-volume data removal solution

This time system due to a large number of historical data caused by slow application response, it is necessary to do a large number of historical data migration, originally planned to backup the original table as a new table, the scale of the replacement of the table to rebuild the index and other solutions, but due to the special structure of the service layer, these tables can not be partitioned, And this way directly will be the service to hang up, so want to use delete data to re-compress the table space and indexed way to resolve.
The estimated amount of deleted data is 12 large tables, each table total is 3000w-3 billion data, need to clear 2/3 of the data, take the steps below, for later finishing

1. Backing up data

The amount of backup data needs tablespace free 500G, now workflow free table space only 90G, need to expand at least 500G
STEP.1 backup failure and complaint main work order
Table name Notes
T367 Fault main work Order T table (Wf:bmcc_eoms_itdealfault)
H367 Fault main work Order H table (Wf:bmcc_eoms_itdealfault)
T633 Complaint main work Order T table (Wf:bj_eoms_complaint)
H633 Complaint main Work Order H table (Wf:bj_eoms_complaint)
Example of a remote Dblink backup statement
Create table T367 as SELECT * from [email protected]_product;
STEP.2 Backup Work Order Auxiliary table
Table name Notes
T117 Work Order Status table (Wf:app_base_infor)
T118 Work order Notification form (Wf:app_base_notice)
T136 Work Order Link table (wf:app_dealprocess)
T135 Work Order Process Line table (Wf:app_deallink)
T134 work order Start end status table (wf:app_dealassistantprocess)
T141 Work Order Process judgment table (WF:APP_DEALVERDICT)
T363 List of work orders (Wf:app_dealgoline)
T114 work form field modification record table (Wf:app_base_fieldmodifylog)

1. Query data Volume

1. Failure T table

Select COUNT (1) from t367 WHERE 1=1 and c3<1380513600

2. Complaint T Form

Select COUNT (1) from t633 WHERE 1=1 and c3<1380513600

2. Other auxiliary tables

Select COUNT (1) from t136  WHERE 1=1and (C700020002 = ' wf:bmcc_eoms_itdealfault ' OR c700020002= ' wf:bj_eoms_complaint ') and C3<1380513600select  count (1) from T135 WHERE 1=1 and (C700020502 = ' Wf:bmcc_eoms_itdealfault ' OR c700020502= ' Wf:bj_eoms_complaint ') and  c3<1380513600; Select  count (1) from T134 WHERE 1=1 and (C700020802 = ' wf:bmcc_ Eoms_itdealfault ' OR c700020802= ' Wf:bj_eoms_complaint ') and  c3<1380513600; Select  count (1) from T141 WHERE 1=1 and (C700020602 = ' wf:bmcc_eoms_itdealfault ' OR c700020602= ' Wf:bj_eoms_complaint ') and  c3<1380513600; Select  count (1) from T114 WHERE 1=1 and (C700021002 = ' wf:bmcc_eoms_itdealfault ' OR c700021002= ' wf:bj_eoms_complaint ') and  c3<1380513600; select count (1) from T118 WHERE 1=1 and (C700050002 = ' Wf:bmcc_eoms_itdealfault ' OR C70005000 2= ' Wf:bj_eoms_complaint ') and  c3>1380513600;

1. New temporary table

Create a new temporary table (rowID to delete data)
Statement:

CREATE table rowid_t633 as select ROWID id,c1 from T633 WHERE C3 < 1380513600CREATE TABLE rowid_t367 as select ROWID ID , C1 from T633 where C3 < 1380513600CREATE TABLE rowid_t117 as SELECT ROWID as ID from T117 where 1=1 and (C700020002 = ' Wf:bmcc_eoms_itdealfault ' OR c700020002= ' Wf:bj_eoms_complaint ') and C3<1380513600create TABLE ROWID_T135 as SELECT ROWID as ID from T135 WHERE 1=1 and (C700020502 = ' wf:bmcc_eoms_itdealfault ' OR c700020502= ' Wf:bj_eoms_c Omplaint ') and c3<1380513600; CREATE TABLE rowid_t134 as SELECT ROWID as ID from T134 WHERE 1=1 and (C700020802 = ' Wf:bmcc_eoms_itdealfault ' OR C700 020802= ' Wf:bj_eoms_complaint ') and c3<1380513600; CREATE TABLE rowid_t141 as SELECT ROWID as ID from T141 WHERE 1=1 and (C700020602 = ' Wf:bmcc_eoms_itdealfault ' OR C700 020602= ' Wf:bj_eoms_complaint ') and c3<1380513600; CREATE TABLE rowid_t114 as SELECT ROWID as ID from T114 WHERE 1=1 and (C700021002 = ' Wf:bmcc_eoms_itdealfault ' OR C7000210 02= ' WF:Bj_eoms_complaint ') and c3<1380513600; CREATE TABLE rowid_t136 as SELECT ROWID as ID from T136 WHERE 1=1 and (C700020002 = ' Wf:bmcc_eoms_itdealfault ' OR C7000200 02= ' Wf:bj_eoms_complaint ') and c3<1380513600; CREATE table rowid_h633 as select ROWID Id,entryid from H633 WHERE T0 < 1380513600CREATE TABLE rowid_h367 as Select ROW ID Id,entryid from H367 where T0 < 1380513600CREATE TABLE rowid_t118 as SELECT ROWID as ID from T118 where 1=1 and (C70 0050002 = ' Wf:bmcc_eoms_itdealfault ' OR c700050002= ' Wf:bj_eoms_complaint ') and c3>1380513600;

1. Write script Delete

   T117

   DECLARE N Number: = 0;
   BEGIN
   for TR in (SELECT ID from rowid_t117) LOOP
   DELETE from T117 WHERE rowid=tr.id;
   n:=n+1;
   IF MOD (n,5000) =0 Then
   COMMIT;
   END IF;
   END LOOP;
   COMMIT;
   END;

T367

DECLARE N Number: = 0; Beginfor tr in (SELECT ID from rowid_t367) loopdelete from T367 WHERE rowid=tr.id;n:=n+1;if MOD (n,5000) =0 thencommit; END IF; END LOOP; COMMIT; END;

T633
DECLARE N Number: = 0;
BEGIN
for TR in (SELECT ID from rowid_t633) LOOP
DELETE from T633 WHERE rowid=tr.id;

N:=n+1;if MOD (n,5000) =0 thencommit; END IF;

END LOOP;
COMMIT;
END;

T134
DECLARE N Number: = 0;
BEGIN
for TR in (SELECT ID from rowid_t134) LOOP
DELETE from T134 WHERE rowid=tr.id;

N:=n+1;if MOD (n,5000) =0 thencommit; END IF;

END LOOP;
COMMIT;
END;
...

1. Remaining table migration Data work steps
   Temporary tables for rebuilding T-tables (T135, T136, T114, T141)

   CREATE TABLE rowid_t135 as SELECT ROWID as ID from T135 WHERE 1=1 and (C700020502 = ' Wf:bmcc_eoms_itdealfault ' OR C7000205 02= ' Wf:bj_eoms_complaint ') and c3<1380513600;
   CREATE TABLE rowid_t141 as SELECT ROWID as ID from T141 WHERE 1=1 and (C700020602 = ' Wf:bmcc_eoms_itdealfault ' OR C7000206 02= ' Wf:bj_eoms_complaint ') and c3<1380513600;
   CREATE TABLE rowid_t114 as SELECT ROWID as ID from T114 WHERE 1=1 and (C700021002 = ' Wf:bmcc_eoms_itdealfault ' OR C7000210 02= ' Wf:bj_eoms_complaint ') and c3<1380513600;
   CREATE TABLE rowid_t136 as SELECT ROWID as ID from T136 WHERE 1=1 and (C700020002 = ' Wf:bmcc_eoms_itdealfault ' OR C7000200 02= ' Wf:bj_eoms_complaint ') and c3<1380513600;

   Execute script to delete data

   DECLARE N Number: = 0;
   BEGIN
   for TR in (SELECT ID from rowid_t135) LOOP
   DELETE from T135 WHERE rowid=tr.id;
   n:=n+1;
   IF MOD (n,5000) =0 Then
   COMMIT;
   END IF;
   END LOOP;
   COMMIT;
   END;
   DECLARE N Number: = 0;
   BEGIN
   for TR in (SELECT ID from rowid_t136) LOOP
   DELETE from T136 WHERE rowid=tr.id;
   n:=n+1;
   IF MOD (n,5000) =0 Then
   COMMIT;
   END IF;
   END LOOP;
   COMMIT;
   END;

DECLARE N Number: = 0;
BEGIN
for TR in (SELECT ID from rowid_t114) LOOP
DELETE from T114 WHERE rowid=tr.id;

N:=n+1;if MOD (n,5000) =0 thencommit; END IF;

END LOOP;
COMMIT;
END;
DECLARE N Number: = 0;
BEGIN
for TR in (SELECT ID from rowid_t141) LOOP
DELETE from T141 WHERE rowid=tr.id;

N:=n+1;if MOD (n,5000) =0 thencommit; END IF;

END LOOP;
COMMIT;
END;

1. Free table Space

   – Open Row lock
   ALTER TABLE T118 enable row movement
   – Organize data and indexes
   Alter TABLE T118 shrink space COMPACT CASCADE
   – Free table Space
   Alter TABLE T118 shrink SPACE CASCADE

Oracle High-volume data removal solution