Easiest way to describe is with one transaction. Your proc could so:


x := systimestamp;
insert into blah values (x);

Even with the check to v$transaction, there is a window of opportunity where things may look "out of sequence".

- we check v$transaction, its empty, and the time is 9:00:00.000000
- we start our export using that time
- then the insert above occurs at 9:00:00.001234
- but the value of *sysdate* it will *store* will be (say) 8.59.59.994623, because it was evaluated ever so slightly before the transaction commenced.

The data is in fact consistent to a point in time, namely, 9:00:00am. But when someone looks at the table "blah" in the source system, they will see a row dated 8.59.59.994623 that will *not* make it into the export, which was at 9am...hence everyone starts screaming about missing data etc etc.

How do you stop this ? With locking.

Your procedure would do:

dbms_lock.request('unique_name');
x := systimestamp;
insert into blah values (x);

and your export job do something like:

dbms_lock.request('unique_name');
start export
dbms_lock.release('unique_name');

would need to request the same lock as well...that way, by definition they will not clash.