Database News You Can Use
A monthly newsletter for Oracle users
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Getting a List of Foreign Keys
You’re invited to submit your Oracle-related questions to us at email@example.com. Please include your name and telephone number in your email.
This month’s question comes to us from Greg in Denver, CO: When I try to purge data from a table in my database, I get the error “ORA-02292: integrity constraint (DCPPROD.SYS_C003565) violated – child record found”. I know what foreign keys are and I understand that I can’t delete a record that is referenced by a row in another table. But how can I figure out what other tables reference the table I’m trying to purge?
Danny Chow of the Database Specialists team responds: With Oracle it is easy to find out what foreign keys are defined on a table, and if there are other tables in the database having foreign keys referencing the particular table. This information is available in the all_constraints and all_cons_columns data dictionary views in every Oracle database. In a complex schema without data model documentation or any development tools such as TOAD around, sorting out foreign key relationships can be a bit challenging. If the constraints were created with system-assigned names (SYS_Cnnnnnn) then this can add to the confusion.
It is not difficult to construct a SQL script to find foreign key information and indeed there are many such scripts available on the internet. A handy script will definitely help to keep the DBA in good health, so we’ll present one here. There is no software to install on your PC to use this script—just a quick login to the Oracle database with SQL*Plus; run the script shown below, and a “quick question” will be a responded by a quick answer. This script works with all releases of Oracle since Oracle 8.
SET LINESIZE 132 SET FEEDBACK OFF SET VERIFY OFF COLUMN con_name FORMAT a30 HEADING "Foreign Key" COLUMN con_col FORMAT a30 HEADING "Local Column" COLUMN r_con FORMAT a65 WRAPPED HEADING "Refers To" COLUMN ref_tab FORMAT a30 HEADING "Referenced By" ACCEPT 1 PROMPT 'Input table owner: ' ACCEPT 2 PROMPt 'Input table name: ' SET HEADING OFF SELECT global_name || ' at ' || TO_CHAR (SYSDATE, 'DD-MON-YY HH24:MI:SS') FROM global_name; SELECT 'Foreign Key Constraints on ' || UPPER ('&1') || '.' || UPPER ('&2') || ':' FROM dual; SET HEADING ON BREAK ON CON_NAME SELECT A.constraint_name con_name, B.column_name con_col, C.table_name || '.' || C.constraint_name || ' (' || C.constraint_type || ')' r_con FROM all_constraints A, all_cons_columns B, all_constraints C WHERE A.owner = UPPER ('&1') AND A.table_name = UPPER ('&2') AND A.constraint_type = 'R' AND B.owner = A.owner AND B.table_name = A.table_name AND B.constraint_name = A.constraint_name AND C.owner = A.r_owner (+) AND C.constraint_name = A.r_constraint_name (+) ORDER BY con_name, B.position; SET HEADING OFF SELECT 'Table ' || UPPER ('&1') || '.' || UPPER ('&2') || ' is Referenced by:' FROM dual; SET HEADING ON BREAK ON con_col SKIP 1 SELECT B.column_name con_col, C.table_name ref_tab, C.constraint_name con_name FROM all_constraints A, all_cons_columns B, all_constraints C WHERE A.owner = UPPER ('&1') AND A.table_name = UPPER ('&2') AND A.constraint_type IN ('P', 'U') AND B.owner = A.owner AND B.constraint_name = A.constraint_name AND C.r_owner = A.owner AND C.r_constraint_name = A.constraint_name AND C.constraint_type = 'R' ORDER BY B.column_name, C.table_name;
Sample output from this script is as follows:
SQL> @table_fkey.sql Input table owner: test Input table name: invoices TESTDB.DBSPECIALISTS.COM at 01-DEC-06 15:39:41 Foreign Key Constraints on TEST.INVOICES: Foreign Key Local Column Refers To -------------------- ----------------- -------------------------- INVOICES_FK1 USER_ID USERS.USERS_PK (P) INVOICES_FK2 PAY_TERMS PAY_TERMS.PAY_TERMS_PK (P) Table TEST.INVOICES is Referenced by: Local Column Referenced By Foreign Key -------------------- ----------------- -------------------------- INVOICE_ID INVOICE_LINES INVOICE_LINES_FK1
Using this script or one like it, you can quickly find all tables in your database that are involved in a declared foreign key relationship with one particular table. This information is the starting point when troubleshooting referential integrity errors such as ORA-02292 and ORA-02291.
We’ve recently added a new script to our website that allows you to take a fast hot backup of your Oracle database using your filer’s snapshot facility. Many filers, such as those from Network Appliance, offer the ability to take a “snapshot” of a filesystem that you can later write to tape or restore as needed. The snapshot is an image of what the filesystem looked like at the moment the snapshot was taken. The filer can create the snapshot in a fraction of a second by simply setting a flag to copy all future block writes to new blocks, thus preserving the accuracy of the snapshot. The script added on our website allows you to take an “instantaneous” hot backup of your Oracle database—you can then copy the snapshot to tape for archival purposes if desired.
This script copies the server parameter file, the control file, and a trace listing of the control file to a directory that will be included in the snapshot. Next the script puts all tablespaces into backup mode and calls upon the filer to create a snapshot. A detailed log report indicates exactly what operations were performed and when, and which archived redo logs would be required to recover the database from the backup set.
This script works on Unix and Linux platforms. It was designed to work with NetApp filers, but can easily be configured to work with other filers as well. You’ll find the script—and other handy scripts from Database Specialists—at http://dbspecialists.wpengine.com/scripts.html.
“Users don’t know what they really want, but they will know when they see it…” recounts a little humorous quote found on www.orafaq.com. It would do us good if we could all pause for a daily laugh—especially during this hectic time of year. The Dilbert “Strip of the Day,” by Scott Adams is always great for a laugh about office politics and the modern workplace. It’s at http://www.dilbert.com.
If you work in technology—and you probably do if you’re reading this—you might enjoy the technology-oriented daily comic strip by J.D. Frazer at http://www.userfriendly.org. With a tagline like: “Impairing productivity since 1997,” you are bound to have a laugh or two while browsing this site.
And finally, here’s another quote to get you in the mood for laughs: “Walking on water and developing software from a specification are easy if both are frozen.” —Edward V. Berard, Life-Cycle Approaches
Where did the time go? We hope you have enjoyed the news and Oracle-related resources we have shared in the monthly issues of The Specialist this year. You can read all back issues at http://dbspecialists.wpengine.com/specialist.html.
We also added some new white papers and scripts this year, and you’ll find all our free resources at http://dbspecialists.wpengine.com/resources.html.
We’d like to extend warm holiday greetings to our clients and colleagues in the Oracle arena. We thank our clients for entrusting us with your mission-critical systems, whether you look to us for all of your database administration and Oracle support needs, or just for backup support or help on specific projects. We are thrilled to contribute to your success in positive ways, and we are proud to be an active part of the Oracle user group community.
To our colleagues, thank you for your questions, participation, and feedback on our various Oracle-related resources. We encourage your input and suggestions. And we look forward to sharing more with you in 2007! Best wishes for the upcoming New Year!