Oracle How to truncate all tables which contain foreign key constraints:

 How to truncate all tables in a schema which contain foreign key constraints:

1. Connect as system to a database, however it is possible to perform these scripts from another account which has got enough privileges.
2. Prepare scripts for disabling and enabling foreign key constraints.
2.1. The first script includes the output of the following query:

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' DISABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as disable_constraints.sql

2.2. The second script includes the output of a very similar query.

SELECT 'ALTER TABLE'||' '||owner||'.'||table_name||' ENABLE CONSTRAINT '||constraint_name||' ;' FROM dba_constraints WHERE constraint_type = 'R' and owner='name of schema' and status = 'ENABLED';

Let's name the output of this query as enable_constraints.sql

3. Prepare scripts for truncating all tables in the schema.
SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='name of schema';

Name the output of this query as truncate_tables.sql

4. Run these scripts in the following order:
@disable_constraints.sql
@truncate_tables.sql
@enable_constraints.sql

That's it.

Thanks,
Sergey.