After you export user schema(s) as user SYS with consistent=yes, you import dump file into database and an Oracle ORA-2298 error is generated. The user schema(s) do not have referenced constraints to other schemas.
From import log following errors found.
ORA-02293: cannot validate (A.CheckConstraint) - check constraint violated, and
ORA-02298: cannot validate (A.ChangesFK) - parent keys not found
Solution of the Problem
If you export any schema as user SYS then simply CONSISTENT parameter is ignored and it is used as CONSISTENT=n whatever value you provide.
According to Oracle documentation, CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.
So if you want to make consistent export then use any other user rather than SYS, for example export as SYSTEM user and use CONSISTENT=y.
However, if your database is big enough once you import data into your database you might not consider re-export dump from source database. Rather either you want to delete orphan child rows from then child table or insert missing rows in the parent table.
In order to find out missing values into the parent table use the following query.
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'
/
The output of this query will be select statement. Copy and paste the output back into sqlplus which will list all of the values that are missing in the parent table.
In order to remove the rows from the child table which contain values that are not in the parent table run the following query,
select 'delete 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'
/
