EXP, consistent=y equals large rollback segs, why?

Re: Export utility and option CONSISTENT

http://download-west.oracle.com/docs...ch01.htm#20622

Extract:
If you use CONSISTENT=Y and the volume of updates is large, the rollback segment will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.



I don't understand why the rollback seg should be large since if CONSISTENT=Y this should block changes taking place on the relevant nested/partitioned table until that table has been exported. Or are these changes put on hold/queued and then applied immediately after the export of the relevant object. Perhaps someone could clarify.


:confused:
Gus

Hey Gus;

Not sure what you mean by queued. I think any updates on those tables would generate (and be stored in) rollback. Than applied after the table is released. I think that is what Oracle is saying here.

"If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions."

MH

So if an export was taking place and CONSISTENT=Y, then you are saying a user who wanted to update a partition of a partitioned table would be allowed to submit a txn updating the table but that the new data from the txn would be stored in the rollback seg until it was clear (export of the partitioned table had completed) to apply the changes to the buffer held copy (req by the export) and ultimately the datafile.

Regds
Gus

If a consistent export is started then the export session has to read data as it was at the beginning of the export, regardless of any changes made. Therefore any changes made to the tables since the start of the export must be preserved in rollback segments until the export finishes.

An inconsistent export does a similar thing, but only for one table at a time -- ie. Changes made to table B while table A is being exported need not be preserved in rollback. Table B's changes only need to be preserved once table B is being exported.

Thanks guys, that seems to make sense with what the doc was saying, just wanted to be sure I was reading it correctly.

Gus

Quote:

---

Originally posted by Gus
. . . the new data from the txn would be stored in the rollback seg until it was clear . . . . to apply the changes to the buffer held copy . . . . and ultimately the datafile.

---

This doesn't read right to me (I hope I haven't changed your meaning by stripping off bits.)

My understanding is that the changes are always applied the same way, independant of any process requiring a consistant view (an export or any other long running select). Which includes updating the buffer immediately and the datafile whenever Oracle sees fit (check-pointing etc). There is no block nor hold nor queue that I know of.

The process requiring the consistant view needs to know not to "believe" what it sees in buffers or on disk, but instead take any relevant before images from the rollback segments. If the rollback is not big enough, I presume this the process that will get an error ("snapshot to old"?) - rather than blocking the updates because you have run out of rollback.

CONSISTENT=Y triggers Oracle to run SET TRANSACTION READ ONLY before exporting every single table or a partition of a table. That's all. Thus, for partitioned tables, even CONSISTENT=Y will not garantee that export is consistent.

Quote:

---

Originally posted by julian
CONSISTENT=Y triggers Oracle to run SET TRANSACTION READ ONLY before exporting every single table or a partition of a table. That's all. Thus, for partitioned tables, even CONSISTENT=Y will not garantee that export is consistent.

---

I believe that it's "serializable",not "read only".

Quote:

---

Originally posted by slimdave
I believe that it's "serializable",not "read only".

---

It's READ ONLY, not SERIALIZABLE.

SET TRANSACTION READ ONLY does two things, it inititiates a transaction, and it assigns a start SCN for the transaction. That start SCN will be used for any query executed in the course of the transaction.