How to find the total number of orphan records in a schema in oracle. Result should be displayed as Child_table,Parent_table, No. of orphan records

Question

use Not Exists while joining both parent and child table – Indiecoder Aug 03 '17 at 10:26 — Indiecoder, Aug 03 '17 at 10:26
Provide sample data and desired results. – Gordon Linoff Aug 03 '17 at 10:31 — Gordon Linoff, Aug 03 '17 at 10:31

score 0 · Answer 1 · answered Aug 03 '17 at 10:31

How do would the database know there is an orphan record?

If you have foreign key constraints set up on the tables then you should not have any orphan records unless you are allowing NULL values in the foreign key or using ON DELETE SET NULL - in which case you just need to go through every foreign key in the data dictionary and count the NULL values in the foreign key columns of those tables.

How to find the total number of orphan records in a schema in oracle. Result should be displayed as Child_table,Parent_table, No. of orphan records

1 Answers1