No, that is not a joke! An empty ORACLE 10g database has 3644 views. There are 49441 objects at all in an empty database. Crazy.

We tried to deploy an application to a customer’s test system yesterday and needed to clean up the full database. We agreed before that they have to delete the schema and the user with all table etc. but they missed to notify their ORACLE adminstrators. We had only the permissions to work in, but not with the schema. So how to drop a few hundred tables?

DROP TABLE * CASCADE CONSTRAINTS!?

No. No such statement is possible. The solution of my workmate was a little funny:

1. Open ORACLE SQL Developer and connect to the database / schema.


2. Mark all tables to delete.


3. Choose “edit / copy full path” from the applications menu.


4. Paste the stuff into the script editor. You get something like “TABLE MySchema.MyTable@MyDb”.


5. Open Search/replace and replace “TABLE MySchema” with “DROP TABLE MySchema”.


6. Replace also “@MyDb” with “ CASCADE CONSTRAINTS;”.


7. Run the script.