interesting question.

[bxr556]

hi, guys:

i can use exp to export several table in same point in time
by using consistent=y.

does anybody have idea how to do it by using PLSQL?

let's say we have table parent, child, grandchild and irrevelant
there are foreign key constraint between parent and child.
and between child and grandchild.
irrevelant have no relationship between other tables.

by use exp consistent=y i can exp those four table at the
exactly point in time.

but what i want to do is
I have another four tables
parent_mirror, child_mirror,grandchild_mirror and irrevelant_mirror, i want to copy the original table to those
mirror tables at the exactly point in time.


one solution i can thought is use serializable transaction.
but it may fail and need indefinitely times retry.
is there any other solution?

also i maybe wrong about how to use serilizable transaction.
can somebody write me a PLSQL to explain how to copy it
to mirror and how to retry( handle the exception of can't serializable)

[kmesser]

consistent = Y guarantees nothing for those related tables. There is no integrity preservation. consistent applies only to one object, not a schema or multiple objects.

[pando]

if consistent=n each table is exported in single transaction therefore consistency is in in single objects
if consistent=y Oracle set the transaction read only therefore all objects exported are consistent

[jmodic]

Originally posted by kmesser
consistent = Y guarantees nothing for those related tables. There is no integrity preservation. consistent applies only to one object, not a schema or multiple objects.

Wrong. CONSISTENT=Y guarantees read consistency (and thus integrity preservation) for all tables included in the export.

As an answer to the original question: why don't you lock all four tables with the single LOCK command? This will ensure the tables are read consistent to the same point in time. Eg

LOCK TABLE parent, child, grandchild, rrevelant IN EXCLUSIVE MODE NOWAIT;

Then you can safely copy read consistent table data to other tables.