Hi Kmesser, 29th April 2001 19:45 hrs chennai

Here is a sample example attached below for chk cons ||rly you can construct one or some one can even give better than this in one capsule(sql script).

I got this from a website.

set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/* */
/* script to spool a listing of all constraint */
/* statements required to rebuild table. */
/* Contents: Table name, col definitions and */
/* storage parameters. */
/* */
/* @c:\oracle\sql\bld_con2.sql */
/* */
/* >> For a specific table name << */
/* >> Only check contraints << */
/* */
/* ************************************************ */

/* set termout off; */
set echo off;
set feedback off;
set long 50;

col dummy noprint format a1;
col dummy2 noprint format a1;
col table_name noprint format a1;
col con noprint format a1;
/* col command format a70; */

/* ********************************* */
/* 1. Generate alter table commands */
/* ********************************* */

/* select 'alter table '||A.owner||'.'||A.table_name||' ADD CONSTRAINT '||A.CONSTRAINT_NAME||' CHECK ('
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%' and /* Exclude NOT NULL */
A.SEARCH_CONDITION is NOT NULL
UNION
select to_char(A.SEARCH_CONDITION)
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%' and /* Exclude NOT NULL */
A.SEARCH_CONDITION is NOT NULL; */

select substr(to_char(A.SEARCH_CONDITION),1,100)
from all_constraints A
where A.owner NOT in ('SYS', 'SYSTEM') and
A.CONSTRAINT_TYPE = 'C' and
A.table_name = upper('&&TABLE_NAME') and
A.CONSTRAINT_NAME not like 'SYS%';

/* undefine TABLE_NAME; */
set heading on;
set pagesize 100;
set termout on;

commit;


Cheers

Padmam