Manage Learn to apply best practices and optimize your operations.

Generating a referential integrity report by table

I need to generate a REFERENTIAL INTEGRITY REPORT BY TABLE (Primary key, Foreign key) of an Oracle database. I have tried to use the following select statement, however it seems to get stuck in an infinite loop.

 select r.table_name rtbl, r.column_name rcol,
 cc.column_name, c.constraint_name
 from user_constraints c,
 	user_cons_columns cc,
 	user_cons_columns r
 where c.constraint_name = cc.constraint_name
 	and c._constraint_name = r.constraint_name
 	and c.constraint_name = 'R'
 order by 1,2

Your query is almost identical to the one that I would have written. It does not get stuck in an infinite loop. The most work required to return your query results is being done to join three views together. By definition, a join on two or more tables will not result in an infinite loop. There will be a termination point. But it may appear to be in an infinite loop due to the fact that it takes so long (seemingly forever) to complete. Given enough time, it will eventually complete.

Unfortunately, there are more than three tables involved here. This query will access the following SYS-owned tables or clusters: CDEF$, ATTRCOL$, USER$, CON$, OBJ$, CCOL$, and COL$. That's a lot of tables to join! And since the USER_CON_COLUMNS view is joined twice, some of those tables will be accessed more than once. This only compounds the problem.

So your query is correct, it will just take a long time to complete. Since you are accessing data dictionary views and tables, there is not much you can do to tune this query. Maybe you want to start it when you leave for the day and come back to it in the morning. Or, you could try to aggregate a step in the results and then process it in steps.

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.