Most of the FK's defined in an Oracle DB, are not created using the on delete cascade option, since that is (most of the times) not wanted at all.

But sometimes records must be deleted while it is not allowed to do so, because there are child records (or a complete tree of child records) present.

For cases like that I use a script that searches the DB for child recs, using the SYS views.

 

The script only works on schema's where technical keys are used, i.e. primary keys that only contain one (numeric) column and the schema must be fully referential defined.

 

Fill in the local parameters SCHEMA_OWNER, MASTER_TABLE and MASTER_TABLE_ID. Execute the script. All underlying records will deleted using a recursive procedure. You can easely adjust the script by adding a cursor to delete multiple masterrecords.

 

enjoy!