Oracle Export/Import
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?
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?
sumit2906
what is the error you are getting?
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.
Also please provide errors or screenshots.
ASKER
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you very much Sumit2906,
It works.
It works.