Click on the banner to learn about and purchase my database training on Azure

Deleting All Objects of an Owner in Oracle Database

Views: 5.041 views
Reading Time: 2 minutes

Good afternoon,

Today I came to bring you a script that I used a lot and made my life a lot faster whenever I had to migrate a system from one instance to another or dump structure and data from one environment to another.

The script prompts for the name of the owner and a directory where it will generate the output files. The result of this script is SQL scripts containing DDL commands to exclude FK constraints, sequences, types, views, tables, procedures, functions, materialized views, public and private synonyms, and purge recyclebin tables. Also, in the generated scripts, it is already spooled to log the commands executed when executing them.

I use this script to delete all objects of an owner to have full control of what I am deleting and to log each object I am deleting. If it is a brave enough DBA, you can use the following command instead of this script:

Let's go to the script drop_schema.sql:

Thank you and see you next time!