Expert: Peter Choi - 5/23/2009

Question
Hello Peter,
Two days ago I asked advise on cleaning out my system user after importing a dump file rather haphazardly.

Your answer consisted -in short- of two options:
1) use the param SHOW=y of the imp command to get the dll's of all the objects imported from the dump file;
2) make use of the 'created' column of dba_objects view to validate the ddl list;

They point me in the right direction, however I have a follow-up question. I queried my db to get all the objects created on the particular date. The result is a list of over > 3700 objects (tables, views, indexes and probably more such as sequences, triggers, etc)...

1) Is there a way to intelligently drop such a list of objects? My homework so far:
a) concatenate the drop statements using excel
b) concatenate the drop statements using a select query:

SELECT 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
FROM user_objects

2) Will such a query  work on every object (i'm thinking primary keys and such)?
3) Am I missing another option?

Again, thanks for your time / advice on this.

Arjen


Answer
Hi Arjen,

Which ever way that you are comfortable with, will work. In the case of using 1a (Excel) you introduce an element of risk (typing mistake as a possibility). With option 1b (Drop using SELECT query) will minimize your risk of typing mistakes. However the explicit DROP CONSTRAINTS will not work.

Instead, in your DROP TABLE DML, you can use this syntax:

DROP TABLE <table_name> CASCADE CONSTRAINT;

(Reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9003.h...)

You can also generate the DML to drop corresponding indexes (should be dropped with a DROP table, but just as a matter of completeness).

Of course, if it is easier to re-create your database than to "undo" your import, that can be another option. (NOTE!!! You will lose all your data. This is a possible option if you didn't have a lot of data before your import).

If you do have a lot of data or work, take a back up before you proceed with the execution of your DROP TABLE DML.

Hope this helps!

Peter