Show all constraints on a table

col type format a10
col cons_name format a30
select	decode(constraint_type,
		'C', 'Check',
		'O', 'R/O View',
		'P', 'Primary',
		'R', 'Foreign',
		'U', 'Unique',
		'V', 'Check view') type
,	constraint_name cons_name
,	status
,	last_change
from	dba_constraints
where	owner like '&owner'
and	table_name like '&table_name'
order by 1
/

List tables that are using the specified table as a foreign key

set lines 100 pages 999
select	a.owner
,	a.table_name
,	a.constraint_name
from	dba_constraints a
,	dba_constraints b
where	a.constraint_type = 'R'
and 	a.r_constraint_name = b.constraint_name
and 	a.r_owner  = b.owner
and 	b.owner = '&table_owner'
and	b.table_name = '&table_name'
/

set lines 100 pages 999
col discon format a100 
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint
'||a.constraint_name||';' discon
from	dba_constraints a
,	dba_constraints b
where	a.constraint_type = 'R'
and 	a.r_constraint_name = b.constraint_name
and	a.r_owner  = b.owner
and 	b.owner = '&table_owner'
and	b.table_name = '&table_name'
/

Produce a list of disabled fk constraints

set lines 100 pages 999
col	table format a60
col	constraint_name format a30
select	owner||'.'||table_name "table"
,	constraint_name
from	dba_constraints
where	status = 'DISABLED'
and	constraint_type = 'R'
and	owner not in ('SYS','SYSTEM')
order by 1,2
/

Produce enable statements all disabled fk constraints

set lines 100 pages 999
select	'alter table '||owner||'.'||table_name||' enable constraint
'||constraint_name||';' "enable"
from	dba_constraints
where	status = 'DISABLED'
and	constraint_type = 'R'
and	owner not in ('SYS','SYSTEM')
order by 1
/

List parent tables that may need fixing/re-importing

select	distinct r.owner || '.' || r.table_name "exp"
from	dba_constraints c
,	dba_constraints r
where	c.status = 'DISABLED'
and	c.constraint_type = 'R'
and	c.r_owner = r.owner
and	c.r_constraint_name = r.constraint_name
and	c.owner not in ('SYS','SYSTEM')
order by 1
/

List missing foriegn key values

select  'select '||cc.column_name-
        ||' from '||c.owner||'.'||c.table_name-
        ||' a where not exists (select ''x'' from '-
        ||r.owner||'.'||r.table_name-
        ||' where '||rc.column_name||' = a.'||cc.column_name||')'
from    dba_constraints c,
        dba_constraints r,
        dba_cons_columns cc,
        dba_cons_columns rc
where   c.constraint_type = 'R'
and     c.owner not in ('SYS','SYSTEM')
and     c.r_owner = r.owner
and     c.owner = cc.owner
and     r.owner = rc.owner
and     c.constraint_name = cc.constraint_name
and     r.constraint_name = rc.constraint_name
and     c.r_constraint_name = r.constraint_name
and     cc.position = rc.position
and     c.owner = '&table_owner'
and     c.table_name = '&table_name'
and     c.constraint_name = '&constraint_name'
order   by c.owner, c.table_name, c.constraint_name, cc.position
/

Show all table constraints for a user

set lines 100 pages 999
break on table_name
select 	table_name
,	decode(constraint_type, 
        	'C', 'Check', 
	        'O', 'R/O View', 
	        'P', 'Primary', 
	        'R', 'Foreign', 
	        'U', 'Unique', 
	        'V', 'Check view') type
,	nvl(index_name, R_CONSTRAINT_NAME) "IDX"
from 	dba_constraints
where	owner like '&user'
order	by table_name
,	decode(constraint_type, 
	'P','0','R','1','U','2','C','3','O','4','V','5')
/