Constraints

Display All Constrainst and Relations
Disable all Referential constraints
DROP Primary Key Constraint
Create Referential constraint for all child tables of a PK constraint
Create Primary key constraint for the parent table
Missing rows from child tables
Missing rows from parent table

Display All Constrainst and Relations

Master table and child tables
-----------------------------
select a.TABLE_NAME||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||') References '||
b.table_name||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by  1
/


Child tables referencing a master table
---------------------------------------
select b.TABLE_NAME||'('||
rtrim(max(decode(d.position,1,d.column_name))||','||
max(decode(d.position,2,d.column_name))||','||
max(decode(d.position,3,d.column_name))||','||
max(decode(d.position,4,d.column_name))||','||
max(decode(d.position,5,d.column_name))||','||
max(decode(d.position,6,d.column_name))||','||
max(decode(d.position,7,d.column_name))||','||
max(decode(d.position,8,d.column_name))||','||
max(decode(d.position,9,d.column_name))||','||
max(decode(d.position,10,d.column_name))||','||
max(decode(d.position,11,d.column_name))||','||
max(decode(d.position,12,d.column_name))||','||
max(decode(d.position,13,d.column_name))||','||
max(decode(d.position,14,d.column_name))||','||
max(decode(d.position,15,d.column_name))||','||
max(decode(d.position,16,d.column_name)),',')||') References '||
a.table_name||'('||
rtrim(max(decode(c.position,1,c.column_name))||','||
max(decode(c.position,2,c.column_name))||','||
max(decode(c.position,3,c.column_name))||','||
max(decode(c.position,4,c.column_name))||','||
max(decode(c.position,5,c.column_name))||','||
max(decode(c.position,6,c.column_name))||','||
max(decode(c.position,7,c.column_name))||','||
max(decode(c.position,8,c.column_name))||','||
max(decode(c.position,9,c.column_name))||','||
max(decode(c.position,10,c.column_name))||','||
max(decode(c.position,11,c.column_name))||','||
max(decode(c.position,12,c.column_name))||','||
max(decode(c.position,13,c.column_name))||','||
max(decode(c.position,14,c.column_name))||','||
max(decode(c.position,15,c.column_name))||','||
max(decode(c.position,16,c.column_name)),',')||')'
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.R_CONSTRAINT_NAME=b.CONSTRAINT_NAME and
a.CONSTRAINT_NAME=c.CONSTRAINT_NAME
and b.CONSTRAINT_NAME=d.CONSTRAINT_NAME
and a.CONSTRAINT_TYPE='R'
and b.CONSTRAINT_TYPE IN ('P', 'U')
group by a.table_name, b.table_name
order by  1
/

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

set serveroutput ON SIZE 1000000

BEGIN
for c1 in(select table_name, constraint_name from user_constraints where constraint_type='P') LOOP
for c2 in(select table_name, constraint_name from user_constraints where constraint_type='R' and r_constraint_name=c1.constraint_name) LOOP
dbms_output.put_line('alter table '||c2.table_name||' drop constraint '||c2.constraint_name||';');
END LOOP;
dbms_output.put_line('alter table '||c1.table_name||' drop constraint '||c1.constraint_name||';');
dbms_output.put_line('---------------------');
END LOOP;
END;
/

set long 999999999
set longchunksize 5000
set linesize 5000
set trimspool ON
set pagesize 0

BEGIN
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
---  dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );		--  If you want to disable storagte option then uncomment this line
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); 
END;
/


select dbms_metadata.get_ddl('REF_CONSTRAINT', 
ref_table.constraint_name, ref_table.owner) 
from
dba_constraints pk_table, 
dba_constraints ref_table
where pk_table.table_name=''                                   --  Filter for table  Name with PK constraint
and   pk_table.owner=''                                        --  Filter for schema Name of the above table
and   ref_table.r_constraint_name=pk_table.constraint_name
and   pk_table.constraint_type in('P', 'U')
and   ref_table.constraint_type='R';

set long 999999999
set longchunksize 5000
set linesize 5000
set trimspool ON
set pagesize 0

BEGIN
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
----  dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );		--  If you want to disable storagte option then uncomment this line
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false ); 
END;
/


select dbms_metadata.get_ddl('CONSTRAINT', pk_table.constraint_name, pk_table.owner) 
from
dba_constraints pk_table
where pk_table.table_name=''                                    --  Filter for table  Name with PK constraint
and   pk_table.owner=''                                         --  Filter for schema Name of the above table
and   pk_table.constraint_type in('P', 'U');

For finding out the rows that are missing in the parent for a Referential constraint column

Create the following table to store the rowid values of the problem rows

create table rows_cons_missing(table_name varchar2(30), constraint_name varchar2(30), missing_row rowid);

---   DELETE rows_cons_missing;

set serveroutput ON

DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'EMPLOYEES';
rtabname varchar2(30);
BEGIN
for cur1 in(select constraint_name, r_constraint_name from user_constraints where table_name=v_tabname and constraint_type='R')
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
select table_name INTO rtabname from user_constraints where constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||rtabname||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/

While deleting records from the master table there can be an excpetion, because 
there are records in the child table referencing the parent table. 

The following script can be used to identify the rows thar are currently referenced by other tables

Here again we use the follwing table for storing the rowid values

SQL> desc rows_cons_missing
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 CONSTRAINT_NAME                                    VARCHAR2(30)
 MISSING_ROW                                        ROWID



---   DELETE rows_cons_missing;


set serveroutput ON

DECLARE
str1 varchar2(1000);
str2 varchar2(1000);
qry1 varchar2(1000);
v_tabname varchar2(30) := 'WEB_CATEGORY';
rtabname varchar2(30);
BEGIN
for cur1 in(select table_name, constraint_name, r_constraint_name from user_constraints where constraint_type='R' and r_constraint_name IN(select constraint_name from user_constraints where constraint_type='P' and table_name=v_tabname))
LOOP
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str1 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.constraint_name and c.constraint_name=cur1.constraint_name;
select rtrim(max(decode(c.position,1,c.column_name))||','||max(decode(c.position,2,c.column_name))||','||max(decode(c.position,3,c.column_name))||','||max(decode(c.position,4,c.column_name))||','||max(decode(c.position,5,c.column_name))||','||max(decode(c.position,6,c.column_name))||','||max(decode(c.position,7,c.column_name))||','||max(decode(c.position,8,c.column_name)),',') INTO str2 from user_constraints a, user_cons_columns c where a.constraint_name=cur1.r_constraint_name and c.constraint_name=cur1.r_constraint_name;
qry1 := 'INSERT INTO rows_cons_missing select '''||v_tabname||''','''||cur1.constraint_name||''',rowid from '||v_tabname||' where ('||str1||') NOT IN (select ('||str2||') from '||cur1.table_name||')';
--dbms_output.put_line(qry1);
execute immediate qry1;
dbms_output.put_line(sql%rowcount||' Rows inserted for constraint '||cur1.constraint_name);
END LOOP;
END;
/

See Also

Manage Locks in Oracle
Scripts to generate schema objects
Using Index Fast Full Scan
Using Returning clause
Managing Tablespaces and Datafiles
Script to reorganize tables
Hidden Parameters in Oracle
Using Multiple block sizes for tablespaces
Index Optimization Scripts
Managing Constraints
Oracle Installation
Oracle Tips
Oracle links
Oracle Books
Indexing strategies
Segment usage statistics