Search For Orphan Associations

The script below will run for a very long time but should ultimately come back with all the BOs that have "orphans" in IBS_SPEC_ASSIGNMENTS and total distinct orphan count for each.

SELECT ist.NAME "BO Name", ist.spec_template_id "BO ID",
COUNT (1) "Orphan Count"
FROM (SELECT DISTINCT spec_id, spec_template_id
FROM (SELECT isa1.spec_id, isa1.spec_template_id
FROM ibs_spec_assignments isa1
WHERE NOT EXISTS (
SELECT 1
FROM ibs_spec ibs1
WHERE ibs1.spec_id =
isa1.spec_id)
UNION ALL
SELECT isa2.ass_spec_id, isa2.ass_spec_template_id
FROM ibs_spec_assignments isa2
WHERE NOT EXISTS (
SELECT 1
FROM ibs_spec ibs2
WHERE ibs2.spec_id =
isa2.ass_spec_id))) orphans
INNER JOIN
ibs_spec_type ist ON ist.spec_template_id = orphans.spec_template_id
GROUP BY ist.NAME, ist.spec_template_id
ORDER BY 3 DESC;