AllExperts > Oracle 
Search      
Oracle
Volunteer
Answers to thousands of questions
 Home · More Oracle Questions · Answer Library  · Encyclopedia ·
More Oracle Answers
Question Library

Ask a question about Oracle
Volunteer
Experts of the Month
Expert Login

Awards

About Us
Tell friends
Link to Us
Disclaimer

 
 
 
 
About Peter 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.

 
   

You are here:  Experts > Computing/Technology > Oracle > Oracle > imp in system..... (2)

Oracle - imp in system..... (2)


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

Add to this Answer   Ask a Question


 
User Agreement | Privacy Policy | Kids' Privacy Policy | Help
Copyright  © 2008 About, Inc. AllExperts, AllExperts.com, and About.com are registered trademarks of About, Inc. All rights reserved.