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, c.table_name, 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.