?
Solved

Clear Oracle 9i tables before import.

Posted on 2008-10-22
5
Medium Priority
?
2,033 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 1500 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

752 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