Dropping an Oracle database schema can take a very long time if you just issue “DROP USER XYZ CASCADE;”. The reason is that all database objects belonging to the schema to be dropped are deleted sequentially.
Below is a little script which drops an Oracle database schema by first dropping all of the objects in the schema in a parallel way. We found this useful für test environments, etc.
Step-by-step guide
- Unpack the archive
- Execute the script “mk_dropusertabs.ksh <USER> <DOP>”, where USER is the schema to be dropped and DOP is the degree of parallelism wanted. For example, “./mk_dropusertabs.ksh SAPSR3 16” to create the drop table command files
- Execute the script “mk_dropuserviews.ksh <USER> <DOP> to create the drop view command files
- Execute “dropusertabes.ksh <USER>
- Execute “dropuserviews.ksh <USER>
- Drop schema: “DROP USER <USER>;”
Script package: dropuserfast.zip