This script accepts the table name and then generates scripts that will disable and enable the foreign keys so you can truncate the table:
set linesize 300;
set pagesize 9999;
set echo off;
set feedback off;
set heading off;
set verify off;
accept tablename prompt "Enter Table Name: ";
spool _disable.fk.constraints.sql
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' DISABLE CONSTRAINTS ' || CONSTRAINT_NAME || ';'
from dba_constraints
where r_constraint_name = (select constraint_name from dba_constraints
where table_name = UPPER('&tablename')
and constraint_type = 'P')
and constraint_type = 'R' ;
spool off
spool _enable.fk.constraints.sql
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' ENABLE CONSTRAINTS ' || CONSTRAINT_NAME || ';'
from dba_constraints
where r_constraint_name = (select constraint_name from dba_constraints
where table_name = UPPER('&tablename')
and constraint_type = 'P')
and constraint_type = 'R' ;
spool off
Execute _disable.fk.constraints.sql, truncate the table and then execute _enable.fk.constraints.sql.
