表PK失效，表中已有重复记录

表PK失效，表中已有重复记录

 

同事发现部分表PK失效，处理过程如下：

查到FM_MIB_OBJ_12215763592失效：
SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where wner=upper('fm54standard') and table_name=upper('fm_mib_object');

OWNER
------------------------------------------------------------
CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
FM54STANDARD
FM_MIB_OBJ_12215763592         P FM_MIB_OBJECT                  DISABLED

查询表所有记录以及非重复记录。
select count(*),count(distinct objectid) from fm54standard.fm_mib_object;
  COUNT(*) COUNT(DISTINCTOBJECTID)
---------- -----------------------
     49564                   48302

SQL> select 49564-48302 from dual;

49564-48302
-----------
       1262

SQL> select count(*) from fm54standard.fm_mib_object where objectid in(select objectid from fm54standard.fm_mib_object group by objectid having count(objectid)>1) and rowid not in  (select min(rowid) from fm54standard.fm_mib_object group by objectid having count(*)>1);

  COUNT(*)
----------
      1262

做备份：
SQL> create table fm54standard.fm_mib_object_bak as select * from fm54standard.fm_mib_object;

Table created.

SQL> select count(*) from fm54standard.fm_mib_object_bak;

  COUNT(*)
----------
     49564

删除重复记录：
delete from fm54standard.fm_mib_object where objectid in (select objectid from fm54standard.fm_mib_object group by objectid having count(objectid)>1) and rowid not in
(select min(rowid) from fm54standard.fm_mib_object group by objectid having count(*)>1);

commit;

启用PK：
alter table fm54standard.fm_mib_object enable constraint FM_MIB_OBJ_12215763592;

 

SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status,LAST_CHANGE,GENERATED from dba_constraints where wner=upper('fm54standard') and table_name=upper('fm_mib_object');

OWNER
------------------------------------------------------------
CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ --------
FM_MIB_OBJ_12215763592         P FM_MIB_OBJECT                  ENABLED

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24996904/viewspace-774057/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24996904/viewspace-774057/