Solved

Clear Oracle 9i tables before import.

Posted on 2008-10-22
5
2,021 Views
Last Modified: 2013-12-19
I have an Oracle 9i database that I need to refesh all the data in the tables of a single schema.  I am moving data from my production server to a replacement server, and now that the testing is complete I need to get them in sync.  The first import I ran sometime back was no problem because no data existed on this replacement server.  Now, when I try to import, it says all the data exist already.  When I used IGNORE=Y, I ended up with duplicate data.  I would like to clear out all the old data before I run the imp command.  This single schema has hundres of tables, so a solution that clears one table at a time will not work.

I have very little experience with Oracle, so please include as much detail as possible.

Thanks.
0
Comment
Question by:sjiinfosys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 

Accepted Solution

by:
MelanieMoore earned 500 total points
ID: 22782161
Before you do the import, you can do something like
sql >  drop user username_being_imported_to cascade;
This will remove all objects and the user
sql > creatae user username_being_imported_to ..;
now do your import and it will be successful.
0
 

Author Comment

by:sjiinfosys
ID: 22785417
Is there a way for me to find out which user owns the Schema I need to clear?
0
 

Author Comment

by:sjiinfosys
ID: 22785891
Found this, "> > In Oracle, the concept of user and schema is (currently) the same."

I was just concearned that the user I was dealing with owned things I didn't want to clear.  Nonetheless, I dropped the user, and then created the user.  Now, when I do the import, no tables get created under the schema.  The errors scroll by too fast in the command window, but I think its an authority issue.  Do I need to do something other than "create user myuser identified by mypassword"?
0
 

Author Comment

by:sjiinfosys
ID: 22786149
Even though the accepted solution did not contain the detail I needed to complete my task (as I mentioned I have very little Oracle DB experience), it did point me in the right direction.  For others with the same problem, I would suggest going to this post "http://www.experts-exchange.com/Databases/Oracle/Q_20953925.html" for the infromation needed to get the imp to work after you perform the drop.
0
 

Author Closing Comment

by:sjiinfosys
ID: 31508967
Thanks for the advise.  It pointed me in the right direction.  The one thing you forgot to mention was I would need to grant the DBA role to the user for the imp to work.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question