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 <mladen_at_wangtrading.com> 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
> > (hkchital_at_singnet.com.sg) 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:jonathan@gennick.com
> >
> > 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 Oracle-article-request_at_gennick.com 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: jonathan_at_gennick.com
> >
> > 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
> >
> > 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: ListGuru_at_fatcity.com (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: mladen_at_wangtrading.com
>
> 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: ListGuru_at_fatcity.com (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: yong321_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Nov 06 2003 - 15:09:27 CST
