set pagesize 300
set linesize 205
set head on
set echo off
spool fkwoind
accept usrid prompt ‘Enter User Name : ‘
col “Cons Name” format a30 heading “Cons Name” word_wrapped
col status format a10 heading status
col “Cons Type” format a12 heading “Cons Type”
col “Column Name” format a30 heading “Column Name”
col “Cons Desc” format a50 heading “Cons Desc” word_wrapped
REM Find all FK Constraints, that does not have column position in constraint and in index matching
select /*+ rule */ b.table_name, b.constraint_name “FK-Cons|Name”,
substr(a.column_name, 1, 30) “Column Name”, a.position, b.status
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and a.owner = b.owner
and a.owner = upper(‘&usrid’)
and b.constraint_type = ‘R’
and (b.table_name, a.column_name, a.position) not in
( select table_name, column_name, column_position
from dba_ind_columns
where table_owner = a.owner
)
order by b.table_name, b.constraint_name, a.position;
REM Find all FK Constraints, that does not have index on it.
select /*+ rule */ b.table_name, b.constraint_name “FK-Cons|Name”,
substr(a.column_name, 1, 30) “Column Name”, b.status
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and a.owner = b.owner
and a.owner = upper(‘&usrid’)
and b.constraint_type = ‘R’
and (b.table_name, a.column_name) not in
( select table_name, column_name
from dba_ind_columns
where table_owner = a.owner
)
order by b.table_name, b.constraint_name, a.position;
spool off