Solved

Clear Oracle 9i tables before import.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure 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