This script will list foreign key constraints for a particular table.

Execution Environment:
     SQL, SQL*Plus

Access Privileges:
     Requires DBA access privileges to be executed.

Usage:
     sqlplus user/

Instructions:
     Copy the script to a file and execute it from SQL*Plus.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

When referential integrity is enforced, to perform data loading or massive  
deletes, it is useful or sometimes necessary to disable the foreign key  
constraints.  
 
You need to disable the foreign keys to load large amounts of data via 
SQL*Loader, Import or SQL script.  The following is a sample script to list 
foreign key(s) for a particular table. 

 ?

rem **************************************************************** 
rem 
rem If necessary, reformat the columns to avoid column wrapping. 
rem 
rem Change the table name USER_CONSTRAINTS to ALL_CONSTRAINTS or  
rem DBA_CONSTRAINTS to change the scope of the query.  
rem 
rem To list the reference on a particular table only, add the table 
rem name to the WHERE clause, i.e., 
rem  
rem    AND A.TABLE_NAME = &tbl_name 
rem 
rem **************************************************************** 
 
column table_name         format a20 
column key_name           format a14 
column referencing_table  format a20 
column foreign_key_name   format a14 
column fk_status          format a8 
 
set linesize 80 
set pagesize 0 
set tab      off 
set space    1 
 
SELECT  
    A.TABLE_NAME table_name, 
    A.CONSTRAINT_NAME key_name, 
    B.TABLE_NAME referencing_table, 
    B.CONSTRAINT_NAME foreign_key_name, 
    B.STATUS fk_status  
  FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B  
  WHERE 
    A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME and 
    B.CONSTRAINT_TYPE = 'R' 
  ORDER BY 1, 2, 3, 4;
  

==============
Sample Output:
==============

SQL> @savedscript
DEPT                 PK_DEPT        EMP                  FK_DEPTNO      ENABLED
ENTITY               SYS_C0013659   ENTITY_REVISION      SYS_C0013665   ENABLE

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.