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
Solved

Clear Oracle 9i tables before import.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect SQL Server from my Oracle database? 11 128
grouping on time windows 6 56
Query to identify changes between rows of two tables 8 55
Help on model clause 5 37
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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 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.

789 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