Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: A special way of migration

Re: A special way of migration

From: Mladen Gogala <>
Date: Wed, 28 Sep 2005 11:33:04 +0000
Message-Id: <>

On 09/28/2005 03:24:39 AM, Andre van Winssen wrote:
> Joerg,
> Here are some thoughts and experiences:
> Exports can be made in a consistent way (consistent=y), meaning that all
> data is logically from the same timestamp, which is what you usually want.

There is, of course, one minor detail here: "consistent=y" will make the whole export "repeatable" and that means "set transaction read only" as the 1st statement. The whole export will be read into undo segments, providing the rapture beyond anything that words can describe to the people trying to perform transactions amd the DBA who will have to size the UNDO tablespace appropriately. That is how read consistency is ensured and that is especially good for the full export of multi-TB databases.

> Imports can be speeded up considerably by using large buffersize (and
> commit=y!) and by making sure no characterset translation has to take place
> between export server and import server. Import isn't happy importing
> datatypes LONG or LONG RAW, it will do it row by row instead of by arrays.
> Sqlplus' COPY feature is a workaround for the slow import using LONG because
> you can use arrays even with LONG's.
> The issue with database links and sqlplus COPY is that you have to arrange
> yourself that copied data across tables in a schema is consistent, i.e. if
> you have to copy tables A..Z then no one else should be updating these
> tables until you are finished. Otherwise the data in the import or target
> server may loose its referential integrity.

That is why God has created the statement "alter table <tbl> enable novalidate constraint <constr>"

Of course, the best thing to do with exp/imp is not to perform them at all. Replication goes a long way. Someone here was explaining to me what Oracle Streams can do. I bought Madhu Thumma's "Oracle Streams" book (Rampant) and I must say it's an excellent book. Now if only my boss will go for that.

Mladen Gogala

Received on Wed Sep 28 2005 - 06:35:18 CDT

Original text of this message