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: Gogala, Mladen <>
Date: Wed, 28 Sep 2005 14:30:08 -0400
Message-ID: <>

How many active/users/transactions you have in the database? I am running an OLTP database and in my case, it's not an exaggeration, at least not by much. Setting the transaction read-only ensures that all reads are consistent with respect to the certain point in time, usually the moment just before the transaction has begun. If there are any other transactions on the database, you need a consistent get, ie a get from the undo segment. If somebody wants to modify block you're reading, he or she has to copy it to an undo block. If you have any long-running transactions, as is the case with billing, you will get a huge amount of undo blocks. Of course, if your database is mostly read-only, you really can have just few blocks of undo space. For the most part, doing export with consistent=yes is inviting those lovely 1555 "snapshot too old" thingies that we all know and love. Of course, to add insult to the injury, there is a bug in which can render your export unusable:

2666174 	Export in direct path can product a corrupt export file if 
            ORA-1555 occurs

So, as far as I am concerned, "consistent=y" amounts to shooting myself in both feet. You go ahead and use it. Go ahead, make my data.
Mladen Gogala
Ext. 121

-----Original Message-----
From: Mark Bole [] 
Sent: Wednesday, September 28, 2005 1:26 PM
Subject: Re: A special way of migration

You stated that "The whole export will be read into undo segments". You 
further stated that setting consistent=y makes "the whole export 
'repeatable'".   These statements are mis-information.

An export, with or without consistent=y, does not consume previously 
unused undo space. (In fact, individual tables are always exported 
consistently regardless of the setting). Undo will be read as needed to 
create a read-consistent view of the data, just as they would for any 
transaction.  If there is very little update activity in the database, 
then very little undo will need to be read.

I just started up a database and began a consistent=y export.

The export file is over 3 GB in size so far (still running).

Here is the undo usage, clearly it has nothing to do with the size of 
the export:

select begin_time, end_time, undoblks from v$undostat;

BEGIN_TIME          END_TIME              UNDOBLKS
------------------- ------------------- ----------
2005/09/28 10:14:11 2005/09/28 10:23:06          3
2005/09/28 10:04:11 2005/09/28 10:14:11         18

Gogala, Mladen wrote:

> Unfortunately, it's not a misinformation. With "consistent=y", you will
> have
> SET TRANSACTION READ ONLY as the first statement in the export. What
> does this statement do you can read for yourself in the manual. It
> wasn't an overuse of sarcasm.
> As far as logical backup goes, I would tend not to disagree, but it has
> nothing to do with CONSISTENT=YES. This "read consistent export" can only
> ensure consistency with respect to certain point in time if it builds a
> read-consistent image of the database with respect to that point in time.
> You have three guesses to guess which structures are used for building
> such a consistent image.
> So Mark, please read the fine manual before you accuse me of being
> overly sarcastic.
> --
> Mladen Gogala
> Ext. 121
-- Mark Bole -- --
Received on Wed Sep 28 2005 - 13:30:16 CDT

Original text of this message