sql - Oracle:如何获得所有可能的约束违规？

Question

我想知道数据库中是否存在会违反所有禁用约束的约束的数据。 (如果启用了约束)

我得到了这样的所有禁用约束:

select * from all_constraints where STATUS='DISABLED';

有解决办法吗？

Answer 1

我找到了一个简单的解决方案:

spool 'disabled_constraint_violations.txt';
set serverout on size unlimited
DECLARE
    sql_enable varchar2(400);
    sql_disable varchar2(400);
BEGIN
   FOR c IN (select OWNER, TABLE_NAME, CONSTRAINT_NAME from all_constraints  where STATUS='DISABLED') LOOP
        BEGIN
        sql_enable := ' alter table ' ||  c.owner||'.'||c.table_name || ' enable constraint ' || c.constraint_name;
        execute immediate  sql_enable ;
        sql_disable := ' alter table ' ||  c.owner||'.'||c.table_name || ' disable constraint ' || c.constraint_name;
        execute immediate  sql_disable ;
        EXCEPTION   
            WHEN OTHERS THEN
                dbms_output.put_line('table: '||c.table_name||'    ||    constraint:  '||c.constraint_name||'    ||    error message:  '||sqlerrm);
        END;
   END LOOP;
END;
set serveroutput off
spool off