Knowledge Base : Drop User Fast (in parallel)

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

  1. Unpack the archive
  2. 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
  3. Execute the script “mk_dropuserviews.ksh <USER> <DOP> to create the drop view command files
  4. Execute “dropusertabes.ksh <USER>
  5. Execute “dropuserviews.ksh <USER>
  6. Drop schema: “DROP USER <USER>;”

Script package: