Thanks for your help I was searching this, it generates the ddl to drop and recreate a constraints and all its dependants. FUNCTION DB_UTL_DDL_CONSTRAINT( cOwner VARCHAR2, cConstraint VARCHAR2) RETURN CLOB IS cReturn CLOB; cReturn2 CLOB; cReturn3 CLOB; cReturn4 CLOB; cTable VARCHAR2(100); cCONSTRAINT_TYPE VARCHAR2(100); BEGIN /* SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' ADD CONSTRAINT '||CONSTRAINT_NAME ||' CHECK ( '||SEARCH_CONDITION|| ')'||CHR(10)||'/' SEARCH_CONDITION FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'C' AND OWNER = 'ADM' */ DBMS_OUTPUT.PUT_LINE('1'); SELECT CONSTRAINT_TYPE, TABLE_NAME,CASE WHEN CONSTRAINT_TYPE = 'R' THEN DBMS_METADATA.GET_DDL('REF_CONSTRAINT',A.CONSTRAINT_NAME,A.OWNER) WHEN CONSTRAINT_TYPE IN ('U','P') THEN DBMS_METADATA.GET_DDL('CONSTRAINT',A.CONSTRAINT_NAME,A.OWNER) END INTO cCONSTRAINT_TYPE,cTable,cReturn FROM DBA_CONSTRAINTS A WHERE OWNER = cOwner AND CONSTRAINT_NAME = cConstraint; DBMS_OUTPUT.PUT_LINE('2'); DBMS_OUTPUT.PUT_LINE(LENGTH(cReturn)); DBMS_OUTPUT.PUT_LINE('3'); IF cCONSTRAINT_TYPE = 'P' THEN FOR A IN (SELECT OWNER,CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME = cConstraint) LOOP -- cReturn := cReturn||chr(10)|| a.owner||'-'||a.constraint_name; SELECT CASE WHEN CONSTRAINT_TYPE = 'R' THEN DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER) WHEN CONSTRAINT_TYPE IN ('U','P') THEN DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME,OWNER) END INTO cReturn2 FROM DBA_CONSTRAINTS WHERE OWNER = A.OWNER AND CONSTRAINT_NAME = A.CONSTRAINT_NAME; cReturn := cReturn || CHR(10)|| '/' ||CHR(10) || cReturn2; SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '|| CONSTRAINT_NAME||';' INTO cReturn3 FROM DBA_CONSTRAINTS WHERE OWNER = A.OWNER AND CONSTRAINT_NAME = A.CONSTRAINT_NAME; cReturn4 := cReturn4 || CHR(10) || cReturn3; END LOOP; END IF; SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '|| CONSTRAINT_NAME||';' INTO cReturn3 FROM DBA_CONSTRAINTS A WHERE OWNER = cOwner AND CONSTRAINT_NAME = cConstraint; cReturn4 := cReturn4 || CHR(10) || cReturn3; cReturn := cReturn4|| CHR(10) || cReturn ; RETURN cReturn ; EXCEPTION WHEN OTHERS THEN RETURN SQLERRM; END; -- //www.freelists.org/webpage/oracle-l