oracle utlexcpt.sql使用笔记

最新推荐文章于 2023-08-03 15:18:49 发布

bjchangxiao

最新推荐文章于 2023-08-03 15:18:49 发布

阅读量229

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/bjchangxiao/article/details/79543053

[oracle@oel ~]$ export ORACLE_SID=orcl
[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 16 16:20:51 2018

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
drop user puzzle cascade;
create user puzzle identified by puzzle;
grant create session,resource to puzzle;
create table puzzle.employees as select * from hr.employees;
conn puzzle/puzzle
alter table employees add constraint pk_emp_id primary key(employee_id);

User dropped.

SQL>
User created.

SQL>
Grant succeeded.

SQL>
Table created.

SQL> Connected.
SQL>
Table altered.

SQL> ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID DISABLE NOVALIDATE;

Table altered.

insert into PUZZLE.EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
2 values (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', to_date('07-06-2002', 'dd-mm-yyyy'), 'AC_MGR', 12008.00, null, 101, 110);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID ENABLE VALIDATE;
ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID ENABLE VALIDATE
*
ERROR at line 1:
ORA-02437: cannot validate (PUZZLE.PK_EMP_ID) - primary key violated

--创建异常表
SQL> @?/rdbms/admin/utlexcpt.sql

Table created.
--启用约束讲重复的数据rowid插入到exceptions表中
SQL> ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID ENABLE VALIDATE EXCEPTIONS INTO EXCEPTIONS;
ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID ENABLE VALIDATE EXCEPTIONS INTO EXCEPTIONS
*
ERROR at line 1:
ORA-02437: cannot validate (PUZZLE.PK_EMP_ID) - primary key violated

SQL> SELECT * FROM EXCEPTIONS;

ROW_ID     OWNER       TABLE_NAME          CONSTRAINT
------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
AAAV1BAAEAAAAIWAAA PUZZLE       EMPLOYEES          PK_EMP_ID
AAAV1BAAEAAAAITAAH PUZZLE       EMPLOYEES          PK_EMP_ID
--新建一个表，把重复的数据插入到新表
SQL> CREATE TABLE EMPLOYEES_DUP
2 AS
3 SELECT * FROM EMPLOYEES
4 WHERE ROWID IN(SELECT ROW_ID FROM EXCEPTIONS);

Table created.

SQL> SELECT * FROM EMPLOYEES_DUP;

EMPLOYEE_ID FIRST_NAME         LAST_NAME      EMAIL         PHONE_NUMBER        HIRE_DATE    JOB_ID    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------- ------------------- -------------------- ---------- -------------- ---------- -------------
205 Shelley         Higgins      SHIGGINS        515.123.8080        2002-06-07 00:00:00 AC_MGR     12008         101     110
205 Shelley         Higgins      SHIGGINS        515.123.8080        2002-06-07 00:00:00 AC_MGR     12008         101     110
--删除重复的数据
SQL> DELETE FROM EMPLOYEES WHERE ROWID IN(SELECT ROW_ID FROM EXCEPTIONS);

2 rows deleted.

SQL> COMMIT;

Commit complete.
--启用约束
SQL> ALTER TABLE EMPLOYEES MODIFY CONSTRAINT PK_EMP_ID ENABLE VALIDATE;

Table altered.
--EMPLOYEES_DUP中包含重复的数据，自己处理，到底需要那条
SQL>