Solved

Clear Oracle 9i tables before import.

Posted on 2008-10-22
5
2,003 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now