AboutPeter Choi Expertise I am a senior Oracle DBA, PeopleSoft Administrator and Project Manager with
10+ years experience. I have been working with PeopleSoft (HRMS 5, 7, 7.x and
8.9), Oracle RDBMS (7.3 - 11gR1) on various Unix and Windows platforms, and some Oracle Application Server (9i/10gR2). I also have experience with the configuration and administration of BEA`s Tuxedo and WebLogic for PeopleSoft 8.x.
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:
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.
