在系統升級的過程中，準備了大量的指令碼，分成幾個視窗來分別執行。
在碰到問題的時候，一定要很細心和冷靜，不經意的錯誤可以需要幾倍，幾十倍的努力來挽回。
準生產環境中有一個表。TREATMENT_ACTIVITY,現在需要從另外一個臨時的schema中insert一部分資料。

Name Null? Type
----------------------------------------- -------- ----------------------------
ENTITY_KEY NOT NULL NUMBER(4)
ENTITY_ID NOT NULL NUMBER(9)
ENTITY_TYPE NOT NULL VARCHAR2(10)
ACTIVITY_CODE NOT NULL VARCHAR2(25)
ACTIVITY_DATE NOT NULL TIMESTAMP(6)
SYS_CREATION_DATE NOT NULL DATE
SYS_UPDATE_DATE DATE
OPERATOR_ID NUMBER(9)
APPLICATION_ID CHAR(6)
DL_SERVICE_CODE CHAR(5)
DL_UPDATE_STAMP NUMBER(4)
APPROVAL_INDICATOR CHAR(1)
MEMO_ID NOT NULL VARCHAR2(100)
REASON_CODE NOT NULL VARCHAR2(6)
STEP_ID VARCHAR2(31)
POLICY_CODE VARCHAR2(128)
PA_IND CHAR(1)

值得一提的是，這個表沒有主鍵，只建立了一些相關的索引，但是執行的時候發現資料有很大的偏差。
插入資料前，有500多萬，需要插入資料400多萬，預計資料應該是1千萬，但是實際卻是1千五百萬。
Initial data of this on before import : 5747945
Data imported from temp schema: 4662877
so we expected to see : 10410822
But the count of this table after imported is 15073699

最後經過測試發現，是在使用Nohup執行一個批次指令碼後，發現有一個表執行的很慢，就手動kill掉了session.手工插入，結果這個插入操作還在後臺執行，結果手工又執行了一遍，因為沒有主鍵，所以沒有發現任何異常，做最後的資料統計的時候才發現。
找到了問題的原因，想把多餘的資料刪除。
首先嚐試使用如下的方式來檢視資料條數。但是竟然給我返回了0條資料。
SQL> select count(*) from TREATMENT_ACTIVITY where (
ENTITY_KEY ,
ENTITY_ID ,
ENTITY_TYPE ,
ACTIVITY_CODE ,
ACTIVITY_DATE ,
SYS_CREATION_DATE ,
SYS_UPDATE_DATE ,
OPERATOR_ID ,
APPLICATION_ID ,
DL_SERVICE_CODE ,
DL_UPDATE_STAMP ,
APPROVAL_INDICATOR ,
MEMO_ID ,
REASON_CODE ,
STEP_ID ,
POLICY_CODE ,
PA_IND ) in (select
19 ENTITY_KEY ,
ENTITY_ID ,
ENTITY_TYPE ,
ACTIVITY_CODE ,
ACTIVITY_DATE ,
SYS_CREATION_DATE ,
SYS_UPDATE_DATE ,
OPERATOR_ID ,
APPLICATION_ID ,
DL_SERVICE_CODE ,
DL_UPDATE_STAMP ,
APPROVAL_INDICATOR ,
MEMO_ID ,
REASON_CODE ,
STEP_ID ,
POLICY_CODE ,
PA_IND from mig_tmp.TREATMENT_ACTIVITY_ext)
/

COUNT(*)
----------
0

我是明明知道資料有重複，但是檢視卻匹配不出來。那使用rowid來做一把測試。
select count(*) from dr_TREATMENT_ACTIVITY t1 where t1.rowid != (select max(rowid) from dr_TREATMENT_ACTIVITY t2
where t1.ENTITY_KEY = t2.ENTITY_KEY
and t1.ENTITY_ID = t2.ENTITY_ID
and t1.ENTITY_TYPE = t2.ENTITY_TYPE
and t1.ACTIVITY_CODE = t2.ACTIVITY_CODE
and t1.ACTIVITY_DATE = t2.ACTIVITY_DATE
and t1.SYS_CREATION_DATE = t2.SYS_CREATION_DATE
and t1.SYS_UPDATE_DATE = t2.SYS_UPDATE_DATE
and t1.OPERATOR_ID = t2.OPERATOR_ID
and t1.APPLICATION_ID = t2.APPLICATION_ID
and t1.DL_SERVICE_CODE = t2.DL_SERVICE_CODE
and t1.DL_UPDATE_STAMP = t2.DL_UPDATE_STAMP
and t1.APPROVAL_INDICATOR = t2.APPROVAL_INDICATOR
and t1.MEMO_ID = t2.MEMO_ID
and t1.REASON_CODE = t2.REASON_CODE
and t1.STEP_ID = t2.STEP_ID
and t1.POLICY_CODE = t2.POLICY_CODE
and t1.PA_IND = t2.PA_IND )

COUNT(*)
----------
0

最後靈機一動，拼成一個字串來。
SQL> select count(*) from TREATMENT_ACTIVITY where (
2 ENTITY_KEY ||
3 ENTITY_ID ||
ENTITY_TYPE ||
ACTIVITY_CODE ||
ACTIVITY_DATE ||
SYS_CREATION_DATE ||
SYS_UPDATE_DATE ||
OPERATOR_ID ||
APPLICATION_ID ||
DL_SERVICE_CODE ||
DL_UPDATE_STAMP ||
APPROVAL_INDICATOR ||
MEMO_ID ||
REASON_CODE ||
STEP_ID ||
POLICY_CODE ||
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 PA_IND ) in (select
19 ENTITY_KEY ||
ENTITY_ID ||
ENTITY_TYPE ||
ACTIVITY_CODE ||
ACTIVITY_DATE ||
SYS_CREATION_DATE ||
SYS_UPDATE_DATE ||
OPERATOR_ID ||
APPLICATION_ID ||
DL_SERVICE_CODE ||
DL_UPDATE_STAMP ||
APPROVAL_INDICATOR ||
MEMO_ID ||
REASON_CODE ||
20 21 22 23 24 25 26 27 28 29 30 31 32 33 STEP_ID ||
34 POLICY_CODE ||
PA_IND from mig_tmp.TREATMENT_ACTIVITY_ext) 35
36 /

COUNT(*)
----------
9325754
Elapsed: 00:01:41.22

這樣就可以直接使用delete來先刪除重複插入的資料。然後重新插入。
delete from TREATMENT_ACTIVITY where (
ENTITY_KEY ||
ENTITY_ID ||
ENTITY_TYPE ||
ACTIVITY_CODE ||
ACTIVITY_DATE ||
SYS_CREATION_DATE ||
SYS_UPDATE_DATE ||
OPERATOR_ID ||
APPLICATION_ID ||
DL_SERVICE_CODE ||
DL_UPDATE_STAMP ||
APPROVAL_INDICATOR ||
MEMO_ID ||
REASON_CODE ||
STEP_ID ||
POLICY_CODE ||
PA_IND ) in (select
ENTITY_KEY ||
ENTITY_ID ||
ENTITY_TYPE ||
ACTIVITY_CODE ||
ACTIVITY_DATE ||
SYS_CREATION_DATE ||
SYS_UPDATE_DATE ||
OPERATOR_ID ||
APPLICATION_ID ||
DL_SERVICE_CODE ||
DL_UPDATE_STAMP ||
APPROVAL_INDICATOR ||
MEMO_ID ||
REASON_CODE ||
STEP_ID ||
POLICY_CODE ||
PA_IND from mig_tmp.TREATMENT_ACTIVITY_ext)

9325754 rows deleted.

Elapsed: 00:02:24.24
SQL> commit;
Commit complete.

SQL> insert into TREATMENT_ACTIVITY select *from mig_tmp.TREATMENT_ACTIVITY_ext;
4662877 rows created.
Elapsed: 00:00:18.44
SQL> commit;
Commit complete.

這樣資料就能完全對上了。這個問題根本原因就是null導致的。可以使用如下的例子來簡單演示一下。
SQL> create table test(id number,name varchar2(10));
insert into test values(1,null);
insert into test values(2,1);
insert into test values(3,1)

create table test2 (id number,name varchar2(10));
insert into test values(1,null);
insert into test values(2,1);
insert into test values(3,1)

Table created.
SQL>
1 row created.
SQL>
1 row created.
SQL> 2 SQL>
Table created.
SQL>
1 row created.
SQL>
1 row created.
SQL>

現在我們來使用相同的方式來查詢。可以看到根本的原因就是null
SQL> select count(*)from test where (id,name) in (select id,name from test2)
2 /
COUNT(*)
----------
0

關於操作失誤的資料修復

相關文章