Home » RDBMS Server » Server Utilities » moving database using exp/imp (oralce 10g on windows 2003)
moving database using exp/imp [message #476479] Thu, 23 September 2010 09:40 Go to next message
enjay
Messages: 15
Registered: September 2010
Junior Member
Hello every one,

I need to move database ORCL into our existing central database CNTR (both are on same OS and oracle version)
I started exp each schema from ORCL and imp in CNTR.
But there is one schema EXMP in database ORCL which also exists in CNTR database with same tables, indexes . The data under schema EXMP in ORCL should be added to schema EXMP in CNTR.
How can I do this?
Your suggestions will be helpful.

Thanks,
enjay
Re: moving database using exp/imp [message #476480 is a reply to message #476479] Thu, 23 September 2010 09:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>The data under schema EXMP in ORCL should be added to schema EXMP in CNTR
Bad idea.
By default, imp will try to append the data, if the table already exists.
If there is a constraint violation, the data will be rejected by table.
Else, you will get duplicated data.

I would never use this in a real time system.
What other options have you considered?
Re: moving database using exp/imp [message #476483 is a reply to message #476480] Thu, 23 September 2010 10:10 Go to previous messageGo to next message
enjay
Messages: 15
Registered: September 2010
Junior Member
Thank you Mahesh for you reply.
So what would be the best way to do this job.
Schema EXMP contain all the details regarding company id and users in both the databases(tables like company_id, company_users).
Since ORCL in now moved into CNTR database.
Now the EXMP should contain all the detail of both ORCL and CNTR. I should not create two separate tables under EXMP.

Thanks,
enjay
Re: moving database using exp/imp [message #476492 is a reply to message #476483] Thu, 23 September 2010 10:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Create an empty table in target.
Apply sql means ( merge statements) using dblinks to validate and pull the data you want.
Not sure if its the best option, atleast it will give you more control programmatically.

Re: moving database using exp/imp [message #476499 is a reply to message #476492] Thu, 23 September 2010 11:03 Go to previous messageGo to next message
enjay
Messages: 15
Registered: September 2010
Junior Member
Mahesh,

"By default, imp will try to append the data, if the table already exists.
If there is a constraint violation, the data will be rejected by table.
Else, you will get duplicated data."

What kind of constraint violation can be expected?

When EXMP schema contains company_id table in CNTR database.
CNTR: imp username/passwd file=exp.dmp fromuser=EXMP touser=EXMP rows=n
Then the data will be appended to table company_id in CNTR database without any corruption to the existing data.

Please correct me if I am wrong.

Thanks.
Re: moving database using exp/imp [message #476500 is a reply to message #476499] Thu, 23 September 2010 11:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>What kind of constraint violation can be expected?
Any constraint defined on table.

Quote:
CNTR: imp username/passwd file=exp.dmp fromuser=EXMP touser=EXMP rows=n
Then the data will be appended to table company_id in CNTR database without any corruption to the existing data.

With rows=n, no rows will be imported.
Re: moving database using exp/imp [message #476512 is a reply to message #476500] Thu, 23 September 2010 11:44 Go to previous messageGo to next message
enjay
Messages: 15
Registered: September 2010
Junior Member
Mahesh,

I need to move the export dump file from my remote server to local server. I heard that I need to FTP it in binary format. Both the servers are on Windows 2003 and Can I use WINSCP software (does WINSCP move the dump file in binary format) Or else Is there any other efficient way to move?

Thanks
Re: moving database using exp/imp [message #476513 is a reply to message #476512] Thu, 23 September 2010 11:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10626
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
WinSCP should work. Any ftp/sftp client should work.
Re: moving database using exp/imp [message #476523 is a reply to message #476499] Thu, 23 September 2010 12:58 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
enjay wrote on Thu, 23 September 2010 12:03
Mahesh,

"By default, imp will try to append the data, if the table already exists.
If there is a constraint violation, the data will be rejected by table.
Else, you will get duplicated data."

What kind of constraint violation can be expected?


One for example would be a unique ID in a table that will have the same value is both databases. We really have no idea as you know your data, you know your table structures. I would NOT just "try" to import and "see what happens."

You'll end up spending days just trying to fix the mess you created.
Re: moving database using exp/imp [message #476524 is a reply to message #476523] Thu, 23 September 2010 13:21 Go to previous message
enjay
Messages: 15
Registered: September 2010
Junior Member
Mahesh and Joy Divison Thank you for your suggestions.
Previous Topic: Oracle Recovery
Next Topic: synonyms compilation error
Goto Forum:
  


Current Time: Tue Sep 23 12:19:22 CDT 2014

Total time taken to generate the page: 0.09730 seconds