Re: Database import (two to one) question

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 23 Aug 2004 16:11:03 GMT
Message-ID: <rGoWc.11261$lI7.5566@newssvr29.news.prodigy.com>

Ana C. Dent wrote:

> li369_at_verizon.net (Jeff) wrote in 
> news:bd11d7fb.0408201348.5c28dd2a_at_posting.google.com:
> 
> 

>>Hi,
>>
>>I have some questions regarding the oracle export and import. We
>>currently have two different database servers on different machines.
>>Oracle 8.1.7 is on our Windows 2000, and oracle 8.0.5 is no NT.
>>
>>Now, we've bought oracle 10g and installed on another machine, Windows
>>2003. We want to move all the data from the above two databases into
>>the new one and combine them to one. I want to do the full export from
>>those two databases respectively and then import them to the new one.
>>Here are my questions:
>>
>>1. Before I do the import, do I need create tablespaces and data files
>>in the new database? If I've already created, will I get errors when I
>>doing the import?

> 
> 
> NO.
> Yes, but with IGNORE=YES the import will not stop & continu to import
> 
> 

>>2. Because I have two export files, can I do two full imports? Does it
>>cause any confliction? In those two database, I have some tables with
>>the same name.
>>

> 
> 
> Yes.
> It depends upon what your goal is &  the contents of the two exports.
> 

For best results, I recommend pre-creating the tablespaces (LMT), users,   roles, and so on using all the shiny new features of 10g. As mentioned here from time to time, Oracle's defaults are not always the best idea.

Then do user-level, not full, exports from the source databases and import them into the appropriate schema(s) in the new database. Trying to import from two export files into the same schema and table will work if you use IGNORE=Y and you don't violate any constraints on the table (rows from the second import will be appended to those from the first).

As a practical matter, you will need to do several dry runs in a test environment, so all the detailed issues will become apparent as you go.

--Mark Bole Received on Mon Aug 23 2004 - 11:11:03 CDT