How to get the metadata DDL of U, P and R type constraints (Doc ID 2289315.1)
Goal
How to get the metadata DDL of U P and R type constraints?
Referential constraint DDL can not be just collected simply using dbms_metadata and the document might be useful if import utility is not able to import constraints from a corrupted dumpfile or character set difference or character corrupted etc..
Basically if the corruption is in metadata, then collect DDL of all the constraints and run in target using below scripts.
Solution
Referential constraint DDL can not be just collected simply using dbms_metadata.
- First create Unique and Primary key constraints
set lines 400
set pages 0
set long 9999999
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform, 'REF_CONSTRAINTS', false);
END;
/
SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
FROM dba_constraints
WHERE owner = '<schema>'
AND constraint_type IN ('U', 'P');
- Once U and P type constraints are created, following the below create R type constraints (reference constraint).
Connect with the same user.
connect <owner>/<password>
set long 1000000
set pages 0
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
'CONSTRAINTS_AS_ALTER', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_transform,
'REF_CONSTRAINTS', false);
END;
/
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name)-----
SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name，owner)-----文档少了owner
FROM dba_tables t
WHERE owner='<schema>'
AND EXISTS (SELECT 1
FROM dba_constraints
WHERE table_name = t.table_name
AND constraint_type = 'R');
- You may compare the counts in both sides once this is created.
select constraint_type,count(*) from dba_constraints where owner='<schema>' group by constraint_type;