ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表PK失效,表中已有重复记录

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

原创 Linux操作系统 作者:linfeng_oracle 时间:2013-10-10 11:00:48 0 删除 编辑
表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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 并行
请登录后发表评论 登录
全部评论

注册时间:2011-09-14

  • 博文量
    76
  • 访问量
    414208