Individuazione delle Constraints associate alle tabelle

La seguente query individua le constraint associata ad ogni tabella

SELECT c.owner, c.table_name, c.constraint_name, c.constraint_type
FROM dba_constraints c, dba_tables t
WHERE c.table_name = t.table_name
AND c.owner not in ('SYS', 'SYSTEM', 'DBSNMP')
AND c.status = 'ENABLED'

La definizione delle constraint è presente in questa tabella.

select distinct constraint_type from user_constraints;

Di seguito il significato.

C - Check constraint on a table
P - Primary key
U - Unique key
R - Referential integrity
V - With check option, on a view
O - With read only, on a view
H - Hash expression
F - Constraint that involves a REF column
S - Supplemental logging

Per disabilitare le constraints eseguire il seguente blocco anonimo.

BEGIN
FOR rec IN
(SELECT a.owner, a.table_name, a.constraint_name
FROM dba_constraints a, dba_tables b
WHERE a.table_name = b.table_name
AND a.owner not in ('SYS', 'SYSTEM', 'DBSNMP')
AND a.status = 'ENABLED'
---AND a.constraint_type = '...'
)
LOOP
dbms_utility.exec_ddl_statement('alter table "' || rec.owner || '"."' ||rec.table_name || '" disable constraint '|| rec.constraint_name);
END LOOP;
END;