Re: Full export and sequence behaviour in 8i

From: Neil Overend <neiloverend_at_gmail.com>
Date: Mon, 16 Mar 2009 08:09:38 +0000
Message-ID: <5acbeade0903160109o8a825e8kf6909224d6049f9c_at_mail.gmail.com>



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_at_gmail.com>:
> 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
Received on Mon Mar 16 2009 - 03:09:38 CDT

Original text of this message