Solved

Oracle Export/Import

Posted on 2010-09-20
7
968 Views
Last Modified: 2012-05-10
Hi experts,
I' m using windows 2008 for Oracle 11G.

I have two databases in Oracle   one is for production and the other is for development.
I' m trying to take export of schema x  from production DB  and import the export file  in schema Y of development  DB.  I take the export of schema X in production DB successfully. But I try  to make import I get errors,
I use the following command,


expdp X/zzzz schemas=X directory='DATA_PUMP_DIR' dumpfile=exp_normal.dmp logfile=exp_normal.log

impdp Y/aaaa  schemas=X  directory=DATA_PUMP_DIR dumpfile=exp_normal.dmp logfile=imp_normal.log  


What is wrong?
0
Comment
Question by:orcun_turkec
[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
7 Comments
 
LVL 7

Expert Comment

by:sumit2906
ID: 33722708
what is the error you are getting?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 33727724
Is your DATA_PUMP_DIR the same location between both instances? Usually it is not unless you specifically configured DATA_PUMP_DIR to be shared. If not, make sure to move the dump file from the DATA_PUMP_DIR from the source instance into the correct location for the destination prior to running the import on the destination.

Also please provide errors or screenshots.
0
 

Author Comment

by:orcun_turkec
ID: 33731899
DATA_PUMP_DIR   locations are same. I checked it   with the command ' SELECT directory_path
FROM all_directories  WHERE directory_name = 'DATA_PUMP_DIR'; ' .
The errors are Ora-39151   and Ora-31684.  

As I said, I  have two databases in Oracle   one is for production and the other is for development.
I' m trying to take export of schema x  from production DB  and import the export file  in schema Y of development  DB.  There is also  schema X in development DB. That' s why I' m trying to import in Schema Y of the Development DB.  In  schema X of  development DB there are  common objects ( .jobs, procedures, triggers, views  etc.)  with the export file taken from Schema X of  production Db.
Actually, what I want to do is, at least import the tables of Schema X of Production DB  to   Schema Y of the  development DB. This is enough for me. Because I' ll just make a comparison between tables.
How can I do this?


 
0
Industry Leaders: 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!

 

Author Comment

by:orcun_turkec
ID: 33731917
In addition to this, there is no table created  in Schema Y of Development DB in order to export tables of schema X of production DB.
0
 
LVL 7

Accepted Solution

by:
sumit2906 earned 500 total points
ID: 33732071
you will have to use REMAP_SCHEMA=X:Y and TABLE_EXISTS_ACTION=REPLACE option:
try this command:
impdp Y/aaaa  schemas=X  directory=DATA_PUMP_DIR dumpfile=exp_normal.dmp logfile=imp_normal.log REMAP_SCHEMA=X:Y TABLE_EXISTS_ACTION=REPLACE
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 33734921
Basically both Ora errors Ora-39151   and Ora-31684 belongs to object exists.
In your import statement, you are not mentioning where to put all these objects, by default it is trying to put in X schema.
As told by sumit, you need to specify target schema, Also check for tablespaces in target and map if requried.
0
 

Author Closing Comment

by:orcun_turkec
ID: 33735596
Thank you very much Sumit2906,

It works.
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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
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 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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 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