Mladen, If the session doesn't start a transaction, I don't see how this can work. First, multiple sessions can find the same SCN, no matter what kind of SCN you're talking about. Secondly, v$datafile.last_change# is set to null unless the datafile is offline.
If each session has its own transaction, i.e. not just a regular query, then their own SCN's at the time the transaction started as shown in v$transaction may be used as a uniqur identifier. But the session can't use a savepoint and hope that has another number in v$transaction; that's not a common requirement though. Yong Huang --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > Well, if the commit is not too frequent, one natural way > of generating primary key would be select max(last_change#) from v$datafile; > These numbers are guaranteed to be unique. As DBWR wakes up every 3 seconds, > the intervals between commit should be at least 3 seconds. As you're very > well > aware, this is the natural mechanism that ensures that any change is properly > > enumerated and, thus, the best and most generic primary key. I understand > that > someone might doubt this mechanism as I would never even dream of using it, > but SCN is the thing that comes naturally. Alternatively, one could produce > SCN > from V$TRANSACTION (base + wrap). > > > On 11/06/2003 12:54:38 PM, Cary Millsap wrote: > > The implementations I've seen all did SELECT...FOR UPDATE. > > Works. > > Doesn't scale. > > > > > > Cary Millsap > > Hotsos Enterprises, Ltd. > > http://www.hotsos.com > > > > Upcoming events: > > - Performance Diagnosis 101: 11/19 Sydney > > - SQL Optimization 101: 12/8-12 Dallas > > - Hotsos Symposium 2004: March 7-10 Dallas > > - Visit www.hotsos.com for schedule details... > > > > > > -----Original Message----- > > Jonathan Gennick > > Sent: Thursday, November 06, 2003 7:59 AM > > To: Multiple recipients of list ORACLE-L > > > > Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale > > ([EMAIL PROTECTED]) wrote: > > HKC> 1. Hit a table that keeps a counter. > > HKC> Used to be a mechanism in the Oracle5 days [If I remember > > correctly, > > HKC> Sequences came in Oracle6]. Issues were with locking the single > > HKC> record used as the generator or scanning for the max(value) of the > > HKC> key. > > HKC> Not quite sure I understand how you encountered concurrency issues, > > though. > > > > My concurrency issues probably boil down to the locking > > business. The app I'm thinking of originally did something > > like: > > > > SELECT counter INTO :1 > > FROM counter_table > > WHERE counter_name = 'table name'; > > > > ...some app code goes here... > > > > UPDATE counter_table > > SET counter := counter+1 > > WHERE counter_name = 'table name'; > > > > Well, it all worked fine in single-user mode<grin>. But it > > was easy enough for me to sit down in front of two > > computers, create two new records, press SAVE at the same > > time, and cause two sessions to grab the same key value, > > because they would both issue the SELECT before either one > > got around to the UPDATE. I couldn't screw things up > > consistently, but just by hitting the SAVE button at the > > same time I could screw things up often enough to make the > > problem obvious. > > > > Maybe there's a way to lock the table, to make the above > > approach work. In my case, I didn't bother trying to find > > that solution. Once I did my little demo, it was easy enough > > to convince the project manager that we should switch to > > using Oracle sequences. > > > > Best regards, > > > > Jonathan Gennick --- Brighten the corner where you are > > http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] > > > > Join the Oracle-article list and receive one > > article on Oracle technologies per month by > > email. To join, visit > > http://four.pairlist.net/mailman/listinfo/oracle-article, > > or send email to [EMAIL PROTECTED] and > > include the word "subscribe" in either the subject or body. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jonathan Gennick > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Cary Millsap > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > Mladen Gogala > Oracle DBA > > > > Note: > This message is for the named person's use only. It may contain > confidential, proprietary or legally privileged information. No > confidentiality or privilege is waived or lost by any mistransmission. If > you receive this message in error, please immediately delete it and all > copies of it from your system, destroy any hard copies of it and notify the > sender. You must not, directly or indirectly, use, disclose, distribute, > print, or copy any part of this message if you are not the intended > recipient. Wang Trading LLC and any of its subsidiaries each reserve the > right to monitor all e-mail communications through its networks. > Any views expressed in this message are those of the individual sender, > except where the message states otherwise and the sender is authorized to > state them to be the views of any such entity. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __________________________________ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
