Re: Full export and sequence behaviour in 8i

Consider this scenario

session 1
select from sequence
insert into table using sequence as Primary Key

session 2

select from sequence
insert into table using sequence as Primary Key

session 1
ROLLBACK

session 2
COMMIT

You've lost the sequence number from session 1 forever. It doesn't
matter how you define your sequence you can always lose sequence
numbers. Whether it be from the above scenario or a database crash
which will automatically rolls back uncommitted transactions.

As for export/import. A consistent=Y export sets the transaction read
only, from that point you get read consistent data, you may get
missing sequence numbers but you will never get the situation where
the sequence is behind data in the tables. You may get snapshot too
old errors but if you don't then I can't see any way the data won't be
consistent. I've done export/import with consistent=Y from live
systems (while in use) to refresh dev/test regularly for the last 5
years and never had a problem

Neil



2009/3/16 Amar Kumar Padhi <amar.padhi@xxxxxxxxx>:
> I have personally used sequences (9i/10g) in no gap scenarios and haven't 
> faced any issue. For sake of completeness I have proper alerts in place to 
> report missing sequences and design takes care of reusing these. users are 
> more than happy about this, though the alert has never puked anything till 
> date and my reuse logic is just waiting to be used.
>
> Yes experience and designs differ.
>
> Thanks
> Amar
>
--
http://www.freelists.org/webpage/oracle-l


Other related posts: