Detect orphaned states and lineages in Oracle

a) Check for Incomplete or missing lineages:

select state_id from sde.states ST where not exists
(select * from sde.state_lineages SL
where ST.lineage_name = SL.lineage_name and SL.lineage_id = 0);

b) Check for Invalid parent state ids:

select state_id from sde.states where parent_state_id not in
(select state_id from sde.states)
order by state_id;

c) Check for States with no lineages:

select distinct state_id from sde.states where lineage_name not in
(select lineage_name from sde.state_lineages)
order by state_id;

select distinct state_id from sde.states where state_id not in
(select lineage_id from sde.state_lineages)
order by state_id;

d)Check for edits referencing states with no parent state:

select state_id from sde.MVTABLES_MODIFIED
where state_id in (select state_id from sde.states where parent_state_id not in
(select state_id from sde.states));