Re: Any way to syncronize sequences between database?
see http://www.cybcon.com/~jkstill/util/reset_sequence/reset_sequence.html "Smith, Ron L." <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/09/2004 11:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Any way to syncronize sequences between database? When doing a partial data refresh, using export/import, is there any way to synchronize "Sequences" between the two databases? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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).
Re: Any way to syncronize sequences between database?
Smith, Ron L. wrote: When doing a partial data refresh, using export/import, is there any way to synchronize "Sequences" between the two databases? Thanks! Ron Ron, We use a perl script that connects to procduction and select the current sequence value then updates the dev sequences to that value. -Brian -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Brian Haas 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).
Any way to syncronize sequences between database?
When doing a partial data refresh, using export/import, is there any way to synchronize "Sequences" between the two databases? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. 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).
Re: sequences and cursors
Run your test case, and check the contents of v$open_cursor. Unless my memory has got it backwards, the pl/sql cursor cache is counted towards max_open_cursors, but the cursors that have been held open by the 'dirty tricks department' are closed as required if the limit is reached: (so should not be responsible for ORA-01000 anyway). cursors held open as session_cache'd cursors are counted independently of max_open_cursors - so should not cause an ORA-01000 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 11, 2003 2:09 PM > It might be held in the cursor cache, it may even be > held in session cursors cache but it will not be counted > as an open cursor. My suggestion had diagnostic purpose only. > The problem is, probably, with the tool which explicitly closes > cursors too frequently and insufficiently sized shared pool > which throws cursors out soon after they're closed. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: sequences and cursors
It might be held in the cursor cache, it may even be held in session cursors cache but it will not be counted as an open cursor. My suggestion had diagnostic purpose only. The problem is, probably, with the tool which explicitly closes cursors too frequently and insufficiently sized shared pool which throws cursors out soon after they're closed. On 12/11/2003 04:54:25 AM, Jonathan Lewis wrote: > > That won't help, as the cursor would still > be held open in the pl/sql cursor cache - > despite the explicit close. > > It's also more efficient to use the implicit > cursor in pl/sql for a single row fetch in > the user's version of Oracle. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > UK___November > > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, December 09, 2003 7:39 PM > > > > What tool are you using? HAve you considered putting select from the > > sequence in an explicit cursor, open it, fetch it and close it again? > > What Have in mind is something like this: > > > > declare > > cursor csr is > > select sai.nextval from dual; > > num integer :=0; > > ind integer :=10; > > begin > > while (ind>=0) loop > > open csr; > > fetch csr into num; > > close csr; > > dbms_output.put_line('Sai is:'||num); > > ind:=ind-1; > > end loop; > > end; > > / > > > > > > Here is the output: > > > > QL> / > > Sai is:13 > > Sai is:14 > > Sai is:15 > > Sai is:16 > > Sai is:17 > > Sai is:18 > > Sai is:19 > > Sai is:20 > > Sai is:21 > > Sai is:22 > > Sai is:23 > > > > PL/SQL procedure successfully completed. > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jonathan Lewis > 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).
Re: sequences and cursors
That won't help, as the cursor would still be held open in the pl/sql cursor cache - despite the explicit close. It's also more efficient to use the implicit cursor in pl/sql for a single row fetch in the user's version of Oracle. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 09, 2003 7:39 PM > What tool are you using? HAve you considered putting select from the > sequence in an explicit cursor, open it, fetch it and close it again? > What Have in mind is something like this: > > declare > cursor csr is > select sai.nextval from dual; > num integer :=0; > ind integer :=10; > begin > while (ind>=0) loop > open csr; > fetch csr into num; > close csr; > dbms_output.put_line('Sai is:'||num); > ind:=ind-1; > end loop; > end; > / > > > Here is the output: > > QL> / > Sai is:13 > Sai is:14 > Sai is:15 > Sai is:16 > Sai is:17 > Sai is:18 > Sai is:19 > Sai is:20 > Sai is:21 > Sai is:22 > Sai is:23 > > PL/SQL procedure successfully completed. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: sequences and cursors
thanks mlade..i will surely give this a shot. can you please tell me whether a sequence creates such issues. as mentioned earlier, the developers claim that no code has changed. im am not able to give any kind of reason for this though the trace shows this statement being called more than 350 times. thanks sai --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > What tool are you using? HAve you considered putting > select from the > sequence in an explicit cursor, open it, fetch it > and close it again? > What Have in mind is something like this: > > declare > cursor csr is > select sai.nextval from dual; > num integer :=0; > ind integer :=10; > begin > while (ind>=0) loop > open csr; > fetch csr into num; > close csr; > dbms_output.put_line('Sai is:'||num); > ind:=ind-1; > end loop; > end; > / > > > Here is the output: > > QL> / > Sai is:13 > Sai is:14 > Sai is:15 > Sai is:16 > Sai is:17 > Sai is:18 > Sai is:19 > Sai is:20 > Sai is:21 > Sai is:22 > Sai is:23 > > PL/SQL procedure successfully completed. > > On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote: > > hi > > here are the specs of my db: > > version 8.1.7 on windows,open_cursors=500 > > i am facing a certain issue. we have a few > sessions > > running out of cursor(ora-1000) and i used 1000 > event > > to dump trace on these sessions. i found a certain > > statement > > select .nextval from dual; > > being repeated more than 350 times of the > available > > 500 cursors. > > the dev say nothing has changed nor has there been > a > > increase in the load. > > > > can you please advise me whther this could be a > issue > > or am i missing something here > > > > thanks > > sai > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Sai Selvaganesan > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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).
Re: sequences and cursors
thanks mladen. will give this a shot...again thanks a bunch sai --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > What tool are you using? HAve you considered putting > select from the > sequence in an explicit cursor, open it, fetch it > and close it again? > What Have in mind is something like this: > > declare > cursor csr is > select sai.nextval from dual; > num integer :=0; > ind integer :=10; > begin > while (ind>=0) loop > open csr; > fetch csr into num; > close csr; > dbms_output.put_line('Sai is:'||num); > ind:=ind-1; > end loop; > end; > / > > > Here is the output: > > QL> / > Sai is:13 > Sai is:14 > Sai is:15 > Sai is:16 > Sai is:17 > Sai is:18 > Sai is:19 > Sai is:20 > Sai is:21 > Sai is:22 > Sai is:23 > > PL/SQL procedure successfully completed. > > On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote: > > hi > > here are the specs of my db: > > version 8.1.7 on windows,open_cursors=500 > > i am facing a certain issue. we have a few > sessions > > running out of cursor(ora-1000) and i used 1000 > event > > to dump trace on these sessions. i found a certain > > statement > > select .nextval from dual; > > being repeated more than 350 times of the > available > > 500 cursors. > > the dev say nothing has changed nor has there been > a > > increase in the load. > > > > can you please advise me whther this could be a > issue > > or am i missing something here > > > > thanks > > sai > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > -- > > Author: Sai Selvaganesan > > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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).
Re: sequences and cursors
What tool are you using? HAve you considered putting select from the sequence in an explicit cursor, open it, fetch it and close it again? What Have in mind is something like this: declare cursor csr is select sai.nextval from dual; num integer :=0; ind integer :=10; begin while (ind>=0) loop open csr; fetch csr into num; close csr; dbms_output.put_line('Sai is:'||num); ind:=ind-1; end loop; end; / Here is the output: QL> / Sai is:13 Sai is:14 Sai is:15 Sai is:16 Sai is:17 Sai is:18 Sai is:19 Sai is:20 Sai is:21 Sai is:22 Sai is:23 PL/SQL procedure successfully completed. On 12/09/2003 01:39:25 PM, Sai Selvaganesan wrote: > hi > here are the specs of my db: > version 8.1.7 on windows,open_cursors=500 > i am facing a certain issue. we have a few sessions > running out of cursor(ora-1000) and i used 1000 event > to dump trace on these sessions. i found a certain > statement > select .nextval from dual; > being repeated more than 350 times of the available > 500 cursors. > the dev say nothing has changed nor has there been a > increase in the load. > > can you please advise me whther this could be a issue > or am i missing something here > > thanks > sai > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Sai Selvaganesan > 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).
sequences and cursors
hi here are the specs of my db: version 8.1.7 on windows,open_cursors=500 i am facing a certain issue. we have a few sessions running out of cursor(ora-1000) and i used 1000 event to dump trace on these sessions. i found a certain statement select .nextval from dual; being repeated more than 350 times of the available 500 cursors. the dev say nothing has changed nor has there been a increase in the load. can you please advise me whther this could be a issue or am i missing something here thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sai Selvaganesan 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).
Re: Sequences CYCLEing -- was RE: How do you genrate primary
In my past experiences we had a PK created from a source location name ie: BOST,followed by the julian date,followed by a sequence number with a max value . The maximun for the pk was 12 characters. Ho would have more than 1 entries in a day from one location. The complete testing of the application with 10001 records proved there could be a pk problem. We silved the problem by converting the sequence to hex and then the max was . Ron >>> [EMAIL PROTECTED] 11/10/2003 3:19:42 PM >>> I created such a beast a few years ago. The 'customer' gave me no choice. The unique ID for a record had to be the current date+Id, and the idea had to start over every day at midnight.That was an interesting bit of code.It certainly wouldn't scale, but this was very low volume OLTP, so we could live with it. And I certainly didn't use this value as the PK. Jared Hemant K Chitale <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/09/2003 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Re: Sequences CYCLEing -- was RE: How do you genrate primary So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant At 08:24 AM 08-11-03 -0800, you wrote: >Being sort of DBA Doubting Tom, I have a bad habit of trying and testing >stuff. Here is what happens with sequences: > >SQL> create sequence test1 start with 1 maxvalue 4 cycle nocache; > >Sequence created. > >SQL> select test1.nextval from dual > 2 / > > NEXTVAL >-- > 1 > >SQL> / > > NEXTVAL >-- > 2 > >SQL> / > > NEXTVAL >-- > 3 > >SQL> / > > NEXTVAL >-- > 4 > >SQL> / > > NEXTVAL >-- > 1 > >SQL> / > > NEXTVAL >-- > 2 > >SQL> > >On 2003.11.08 10:54, Hemant K Chitale wrote: >>Ah yes. The exception case when sequence numbers are not unique. >>Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the >>the number would never exceed 4 digits and didn't want to "waste resources >>and space"]. >>And I do vaguely remember that I HAVE seen a Sequence CYCLE over and >>restart. Can't remember the details, though this was many years ago. >>It takes all kinds of developers and database designers to make Oracle >>interesting. >>Hemant >>At 03:29 PM 05-11-03 -0800, you wrote: >>>In theory I suppose it's possible to have overlaps, but this has nothing to >>>do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND >>>set MAXVALUE to something less than reasonable (the default is NOMAXVALUE >>>which IIRC means 10 to the power 27) AND don't create a unique index on the >>>column storing the sequence, then maybe you can end up with multiple rows >>>having the same value? Never heard of anyone doing that, of course, but in >>>theory ... >>>Pete >>>"Controlling developers is like herding cats." >>>Kevin Loney, Oracle DBA Handbook >>>"Oh no, it's not. It's much harder than that!" >>>Bruce Pihlamae, long-term Oracle DBA >>> >>>-Original Message- >>>Millsap >>>Sent: Thursday, November 06, 2003 7:34 AM >>>To: Multiple recipients of list ORACLE-L >>> >>>I've never heard of an Oracle sequence not generating unique id's, OPS/RAC >>>or not. Gaps, yes. Overlaps, never. >>> >>>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- >>>Todd Boss >>>Sent: Wednesday, November 05, 2003 1:09 PM >>>To: Multiple recipients of list ORACLE-L >>>There's six very good reasons listed below to NOT use SSN as your unique >>>PK, >>>and honestly I can't believe this is STILL an issue for any dba >>>who deals w/ SSNs. These arguments are YEARS old. Isn't this Data >>>Modelling 101? I know for sure this exact case is in every text i've read. >>>How to deal with Natural keys: >>>- Create a surrogate PK that the user never sees but guarantees uniquenes
Re: Sequences CYCLEing -- was RE: How do you genrate primary
I created such a beast a few years ago. The 'customer' gave me no choice. The unique ID for a record had to be the current date+Id, and the idea had to start over every day at midnight. That was an interesting bit of code. It certainly wouldn't scale, but this was very low volume OLTP, so we could live with it. And I certainly didn't use this value as the PK. Jared Hemant K Chitale <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 11/09/2003 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: Sequences CYCLEing -- was RE: How do you genrate primary So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant At 08:24 AM 08-11-03 -0800, you wrote: >Being sort of DBA Doubting Tom, I have a bad habit of trying and testing >stuff. Here is what happens with sequences: > >SQL> create sequence test1 start with 1 maxvalue 4 cycle nocache; > >Sequence created. > >SQL> select test1.nextval from dual > 2 / > > NEXTVAL >-- > 1 > >SQL> / > > NEXTVAL >-- > 2 > >SQL> / > > NEXTVAL >-- > 3 > >SQL> / > > NEXTVAL >-- > 4 > >SQL> / > > NEXTVAL >-- > 1 > >SQL> / > > NEXTVAL >-- > 2 > >SQL> > >On 2003.11.08 10:54, Hemant K Chitale wrote: >>Ah yes. The exception case when sequence numbers are not unique. >>Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the >>the number would never exceed 4 digits and didn't want to "waste resources >>and space"]. >>And I do vaguely remember that I HAVE seen a Sequence CYCLE over and >>restart. Can't remember the details, though this was many years ago. >>It takes all kinds of developers and database designers to make Oracle >>interesting. >>Hemant >>At 03:29 PM 05-11-03 -0800, you wrote: >>>In theory I suppose it's possible to have overlaps, but this has nothing to >>>do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND >>>set MAXVALUE to something less than reasonable (the default is NOMAXVALUE >>>which IIRC means 10 to the power 27) AND don't create a unique index on the >>>column storing the sequence, then maybe you can end up with multiple rows >>>having the same value? Never heard of anyone doing that, of course, but in >>>theory ... >>>Pete >>>"Controlling developers is like herding cats." >>>Kevin Loney, Oracle DBA Handbook >>>"Oh no, it's not. It's much harder than that!" >>>Bruce Pihlamae, long-term Oracle DBA >>> >>>-Original Message- >>>Millsap >>>Sent: Thursday, November 06, 2003 7:34 AM >>>To: Multiple recipients of list ORACLE-L >>> >>>I've never heard of an Oracle sequence not generating unique id's, OPS/RAC >>>or not. Gaps, yes. Overlaps, never. >>> >>>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- >>>Todd Boss >>>Sent: Wednesday, November 05, 2003 1:09 PM >>>To: Multiple recipients of list ORACLE-L >>>There's six very good reasons listed below to NOT use SSN as your unique >>>PK, >>>and honestly I can't believe this is STILL an issue for any dba >>>who deals w/ SSNs. These arguments are YEARS old. Isn't this Data >>>Modelling 101? I know for sure this exact case is in every text i've read. >>>How to deal with Natural keys: >>>- Create a surrogate PK that the user never sees but guarantees uniqueness. >>>- Create a separate (unique if you can) index on your "natural key." >>>- Go on with life. >>>I'm a bit more concerned about what i'm hearing about Sequences. Is it true >>>that sequences are NOT guaranteed to be unique?? After all >>>this time listening to Oracle people scoff at the Sybase/Ms Sql identity >>>feature and its inadequacies as compared to Sequences for generating >>>sequential surrogate keys they&
Re[2]: Sequences CYCLEing -- was RE: How do you genrate
Slightly OT: Way before AR was available, we built a set of triggers to do the replication job (Oracle 7.0.something). Of course synchronisation of sequences was a hell of a job. Lucky for us, the system was more DSS than OLTP: approx. 3000 - 10.000 transactions/day. Furthermore there was a real Master/Slave database architecture, the slave would only be queried, and wasn't allowed to be updated from ordinary users. This was enforced by pre-DML triggers, which prevented any DML to be executed except those coming form the replication process. Because this was a flight information display system, running on several airports, high availabilty was the goal. All primary keys were meaningless, a 9 digit number, and generated from a sequence by a pre-insert trigger. Because of some home-grown GUI program, used in the project, the key needed to be unique in the whole set of tables, so onde qequence was used. Ordering was important, gaps were no problem. The same pre-insert-trigger would find a non-null primary key when the record came from the replication process. If a non-null primary key was found, it would start a loop hammering the sequence until it reached the same id as the id just received. So we enforced synchronisation between both sequences, without the need of having them started at different offsets, which would have violated the 'ordered constraint' needed by the GUI-stuff. Carel-Jan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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).
Re[2]: Sequences CYCLEing -- was RE: How do you genrate primary
Sunday, November 9, 2003, 10:44:25 AM, Stephane Faroult ([EMAIL PROTECTED]) wrote: SF> The big advantage on dropping and recreating them is that SF> existing privileges stay in place and you don't have to GRANT SELECT to SF> everybody ... Yeah, I wrote a script one to let me adjust sequences in the manner you've just described. I should try and dig that up, though I think it's long-lost. 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).
Re: Sequences CYCLEing -- was RE: How do you genrate primary
I've seen several of them on projects I worked on: People converted older data-models - if model is the right name ;-) - and stuck to the small key-fields they had. One particlaur example got a nice :-( algorithm replaced by this nice lick Oracle feature called 'sequence'. Of course increasing marketing success caused an increasing need for unique keys, causing exact the cycling problem described. Lesson learned: make a calculation in advance: Most of the time 9 digits will do the job: it will let you generate 1 key per second, 86400 seconds a day, 11574 days. This is approx. 31 years and 8 months, and that migh be sufficient. If not, simply add some extra digits. Just get some proof that your choise is right! Of course this will not work out when you insert 6000 rows per second, all day long, but then you might have other problems ;-). It's amazing how people (calling themself programmers) are putting together some code, without any idea of some future behaviour of their code! (I remember some Y2K problem a few years ago. I hate to say this, but all my code, as from where I started working in IT back in 1982) was Y2K proof. However, I must admit, my older C-code won't survive the next 'millenium'-problem, when the unix-date-format can't follow the 'seconds + 01-01-1970' format. Maybe 64-bit compilers will resolve that problem. But, mark my words ;-) there will be some concern, dataconversion and other familiar stuff which will us, elderly and bitter software veterans give some deja-vu feelings right then! Carel-Jan At 07:04 9-11-03 -0800, you wrote: So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile +31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
Re: Sequences CYCLEing -- was RE: How do you genrate primary
Hemant K Chitale wrote: > > So, let's start another thread. > > How many of you have actually seen Sequences implemented in the manner I > described > and Mladen demonstrated below ? > > Hemant > What I have seen used are non-cycling sequences which are forced to cycle - the idea is to restart the numbering from 1 everyday. So, everyday at midnight the sequences are ALTERed so that their maximum is today's maximum, and they are forced to return to 1 - before making them NOCYCLE again. The big advantage on dropping and recreating them is that existing privileges stay in place and you don't have to GRANT SELECT to everybody ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
Re: Sequences CYCLEing -- was RE: How do you genrate primary
So, let's start another thread. How many of you have actually seen Sequences implemented in the manner I described and Mladen demonstrated below ? Hemant At 08:24 AM 08-11-03 -0800, you wrote: Being sort of DBA Doubting Tom, I have a bad habit of trying and testing stuff. Here is what happens with sequences: SQL> create sequence test1 start with 1 maxvalue 4 cycle nocache; Sequence created. SQL> select test1.nextval from dual 2 / NEXTVAL -- 1 SQL> / NEXTVAL -- 2 SQL> / NEXTVAL -- 3 SQL> / NEXTVAL -- 4 SQL> / NEXTVAL -- 1 SQL> / NEXTVAL -- 2 SQL> On 2003.11.08 10:54, Hemant K Chitale wrote: Ah yes. The exception case when sequence numbers are not unique. Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the the number would never exceed 4 digits and didn't want to "waste resources and space"]. And I do vaguely remember that I HAVE seen a Sequence CYCLE over and restart. Can't remember the details, though this was many years ago. It takes all kinds of developers and database designers to make Oracle interesting. Hemant At 03:29 PM 05-11-03 -0800, you wrote: In theory I suppose it's possible to have overlaps, but this has nothing to do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND set MAXVALUE to something less than reasonable (the default is NOMAXVALUE which IIRC means 10 to the power 27) AND don't create a unique index on the column storing the sequence, then maybe you can end up with multiple rows having the same value? Never heard of anyone doing that, of course, but in theory ... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, November 06, 2003 7:34 AM To: Multiple recipients of list ORACLE-L I've never heard of an Oracle sequence not generating unique id's, OPS/RAC or not. Gaps, yes. Overlaps, never. 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- Todd Boss Sent: Wednesday, November 05, 2003 1:09 PM To: Multiple recipients of list ORACLE-L There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your "natural key." - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd > > I'm fully convinced. SSN should not be used as a PK. > > Can we also conclude that natural keys in general are only good if you sit in > an ivory tower and do unrealistic lab test? > > Yong Huang > > --- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote: > > Having worked for the government in a situation where we were actually > > tracking information BY Social Security Number, let me tell you the problems > > with it. > > > > 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE > > 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security > > Number > > 3) Not all Social Security Numbers are numeric > > 4) Not all Social Security Numbers which ARE numeric are 9 characters in > > length > > 5) Social Security Numbers can be changed by the holder > > 6) It is illegal to use the Social Security Number for any purpose other > > than that which the government specifically uses Social Security Numbers for > > (ie., the distribution of benefits). I'll bet *that* one is strictly > > enforced. > > > > HTH, > > Bambi. > > > > -Original Message- > >
Re: Sequences CYCLEing -- was RE: How do you genrate primary keys?
Being sort of DBA Doubting Tom, I have a bad habit of trying and testing stuff. Here is what happens with sequences: SQL> create sequence test1 start with 1 maxvalue 4 cycle nocache; Sequence created. SQL> select test1.nextval from dual 2 / NEXTVAL -- 1 SQL> / NEXTVAL -- 2 SQL> / NEXTVAL -- 3 SQL> / NEXTVAL -- 4 SQL> / NEXTVAL -- 1 SQL> / NEXTVAL -- 2 SQL> On 2003.11.08 10:54, Hemant K Chitale wrote: Ah yes. The exception case when sequence numbers are not unique. Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the the number would never exceed 4 digits and didn't want to "waste resources and space"]. And I do vaguely remember that I HAVE seen a Sequence CYCLE over and restart. Can't remember the details, though this was many years ago. It takes all kinds of developers and database designers to make Oracle interesting. Hemant At 03:29 PM 05-11-03 -0800, you wrote: In theory I suppose it's possible to have overlaps, but this has nothing to do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND set MAXVALUE to something less than reasonable (the default is NOMAXVALUE which IIRC means 10 to the power 27) AND don't create a unique index on the column storing the sequence, then maybe you can end up with multiple rows having the same value? Never heard of anyone doing that, of course, but in theory ... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, November 06, 2003 7:34 AM To: Multiple recipients of list ORACLE-L I've never heard of an Oracle sequence not generating unique id's, OPS/RAC or not. Gaps, yes. Overlaps, never. 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- Todd Boss Sent: Wednesday, November 05, 2003 1:09 PM To: Multiple recipients of list ORACLE-L There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your "natural key." - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd > > I'm fully convinced. SSN should not be used as a PK. > > Can we also conclude that natural keys in general are only good if you sit in > an ivory tower and do unrealistic lab test? > > Yong Huang > > --- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote: > > Having worked for the government in a situation where we were actually > > tracking information BY Social Security Number, let me tell you the problems > > with it. > > > > 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE > > 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security > > Number > > 3) Not all Social Security Numbers are numeric > > 4) Not all Social Security Numbers which ARE numeric are 9 characters in > > length > > 5) Social Security Numbers can be changed by the holder > > 6) It is illegal to use the Social Security Number for any purpose other > > than that which the government specifically uses Social Security Numbers for > > (ie., the distribution of benefits). I'll bet *that* one is strictly > > enforced. > > > > HTH, > > Bambi. > > > > -Original Message- > > Sent: Wednesday, November 05, 2003 8:00 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Tom, > > > > I think using a natural key
Sequences CYCLEing -- was RE: How do you genrate primary keys?
Ah yes. The exception case when sequence numbers are not unique. Believe me, I've seen Sequences with low MAXVALUE [the guy decided that the the number would never exceed 4 digits and didn't want to "waste resources and space"]. And I do vaguely remember that I HAVE seen a Sequence CYCLE over and restart. Can't remember the details, though this was many years ago. It takes all kinds of developers and database designers to make Oracle interesting. Hemant At 03:29 PM 05-11-03 -0800, you wrote: In theory I suppose it's possible to have overlaps, but this has nothing to do with OPS/RAC. If you create the sequence to CYCLE (not the default) AND set MAXVALUE to something less than reasonable (the default is NOMAXVALUE which IIRC means 10 to the power 27) AND don't create a unique index on the column storing the sequence, then maybe you can end up with multiple rows having the same value? Never heard of anyone doing that, of course, but in theory ... Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Thursday, November 06, 2003 7:34 AM To: Multiple recipients of list ORACLE-L I've never heard of an Oracle sequence not generating unique id's, OPS/RAC or not. Gaps, yes. Overlaps, never. 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- Todd Boss Sent: Wednesday, November 05, 2003 1:09 PM To: Multiple recipients of list ORACLE-L There's six very good reasons listed below to NOT use SSN as your unique PK, and honestly I can't believe this is STILL an issue for any dba who deals w/ SSNs. These arguments are YEARS old. Isn't this Data Modelling 101? I know for sure this exact case is in every text i've read. How to deal with Natural keys: - Create a surrogate PK that the user never sees but guarantees uniqueness. - Create a separate (unique if you can) index on your "natural key." - Go on with life. I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? After all this time listening to Oracle people scoff at the Sybase/Ms Sql identity feature and its inadequacies as compared to Sequences for generating sequential surrogate keys they're NOT guaranteed to be unique if you're working in a parallel processing environment?? Is this really true? Do Oracle developers have to depend on circa 1990 techniques to generate something as BASIC as a surrogate key by designing their own little lookup table systems? Or am I just reading this thread incorrectly? Todd > > I'm fully convinced. SSN should not be used as a PK. > > Can we also conclude that natural keys in general are only good if you sit in > an ivory tower and do unrealistic lab test? > > Yong Huang > > --- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote: > > Having worked for the government in a situation where we were actually > > tracking information BY Social Security Number, let me tell you the problems > > with it. > > > > 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE > > 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security > > Number > > 3) Not all Social Security Numbers are numeric > > 4) Not all Social Security Numbers which ARE numeric are 9 characters in > > length > > 5) Social Security Numbers can be changed by the holder > > 6) It is illegal to use the Social Security Number for any purpose other > > than that which the government specifically uses Social Security Numbers for > > (ie., the distribution of benefits). I'll bet *that* one is strictly > > enforced. > > > > HTH, > > Bambi. > > > > -Original Message- > > Sent: Wednesday, November 05, 2003 8:00 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Tom, > > > > I think using a natural key such as Soc. Sec. # as the primary key is a good > > idea. You don't need to maintain the sequence so there's no performance > > issue > > associated with sequences. There's no issue of gaps. No index root block > > contention. It doesn't seem to be industry common practice though. > > > > In your college student case, changing primary keys is rare so it's not a > > big > > problem. > > > > Yong Huang > > > > --- "Mercadante, Thomas F" <[EMAIL
RE: Re[2]: ORDER -- was Re[2]: Sequences in OPS/RAC
How about removing any holes via some sort of batch process, ie: Insert into staging tables using any old sequence, don't worry about gaps. Then, periodically upload this to the main tables using either a sequence or a local pl/sql counter. I can think of a number of variations on this theme. Regards Daid Lord > -Original Message- > From: Thater, William [mailto:[EMAIL PROTECTED] > Sent: 06 November 2003 14:49 > To: Multiple recipients of list ORACLE-L > Subject: RE: Re[2]: ORDER -- was Re[2]: Sequences in OPS/RAC > > > Jonathan Gennick scribbled on the wall in glitter crayon: > > > Would NOCACHE really prevent loss of sequence values? It > seems to me > > that you could still find yourself in a situation where you grab > > NEXTVAL from a sequence, causing it to increment, and then you > > rollback your transaction. The sequence, of course, would not > > rollback, and you'd "lose" a value. I don't know any way > around this > > problem using sequences. If it were a requirement not to lose any > > values, none at all, then I'd probably at least think about > > alternative solutions. > > OK so outside of the select-from-a-table-increment-and-put-it-back > solutions, what other ones are there that absolutely not have > gaps. i don't think even that one can make that claim. > > -- > Bill "Shrek" Thater ORACLE DBA > "I'm going to work my ticket if I can..." -- Gilwell song > [EMAIL PROTECTED] > -- > -- > We have penetrated far less deeply into the regularities > obtaining within the realm of living things, but deeply > enough nevertheless to sense at least the rule of fixed > necessity . what is still lacking here is a grasp of the > connections of profound generality, but not a knowledge of > order itself. > - Albert Einstein > -- *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential. If they have come to you in error you must take no action based on them, nor must you copy or show them to anyone; please reply to this e-mail and highlight the error. Please note that this e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. We advise that you understand and observe this lack of security when e-mailing us. Steps have been taken to ensure this e-mail and attachments are free from any virus, but advise the recipient to ensure they are actually virus free. The views, opinions and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by Iron Mountain. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord David 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).
RE: Re[2]: ORDER -- was Re[2]: Sequences in OPS/RAC
Jonathan Gennick scribbled on the wall in glitter crayon: > Would NOCACHE really prevent loss of sequence values? It > seems to me that you could still find yourself in a > situation where you grab NEXTVAL from a sequence, causing it > to increment, and then you rollback your transaction. The > sequence, of course, would not rollback, and you'd "lose" a > value. I don't know any way around this problem using > sequences. If it were a requirement not to lose any values, > none at all, then I'd probably at least think about > alternative solutions. OK so outside of the select-from-a-table-increment-and-put-it-back solutions, what other ones are there that absolutely not have gaps. i don't think even that one can make that claim. -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] We have penetrated far less deeply into the regularities obtaining within the realm of living things, but deeply enough nevertheless to sense at least the rule of fixed necessity . what is still lacking here is a grasp of the connections of profound generality, but not a knowledge of order itself. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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).
Re[2]: ORDER -- was Re[2]: Sequences in OPS/RAC
Wednesday, November 5, 2003, 9:14:34 AM, Hemant K Chitale ([EMAIL PROTECTED]) wrote: HKC> There are times when you cannot afford to lose CACHed values, as John HKC> Kanagaraj has pointed out HKC> in Oracle Applications when generating Cheque numbers. Such sequences HKC> required a patch in HKC> Oracle Apps 10.7 and 11 and/or creation with NOCACHE. Would NOCACHE really prevent loss of sequence values? It seems to me that you could still find yourself in a situation where you grab NEXTVAL from a sequence, causing it to increment, and then you rollback your transaction. The sequence, of course, would not rollback, and you'd "lose" a value. I don't know any way around this problem using sequences. If it were a requirement not to lose any values, none at all, then I'd probably at least think about alternative solutions. 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).
RE: ORDER -- was Re[2]: Sequences in OPS/RAC
ORDER isn't strictly necessary when all you want are unique numbers. There are times when you cannot afford to lose CACHed values, as John Kanagaraj has pointed out in Oracle Applications when generating Cheque numbers. Such sequences required a patch in Oracle Apps 10.7 and 11 and/or creation with NOCACHE. An application where you need ORDER is when you are inserting new rows and the sequence number must match the insertion temporaly -- ie, function like a timestamp so that you can fetch the same rows in the same sequence. Hemant At 09:44 AM 03-11-03 -0800, you wrote: Hi, I have RAC and I always use ORDER when I create SEQUENCE. The following information is from Oracle Manual: ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order. Muqthar Ahmed -Original Message- Sent: Monday, November 03, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC> However, the Builder.Com article quite explicity asserts HKC> "Sequence generator numbers are guaranteed to be unique only for a single HKC> instance, which is unsuitable for use as a primary key in parallel or HKC> remote environments, where a sequence in each environment might generate HKC> the same number and result in conflicts Can you point us to the article? My guess is that the author is not familiar with Oracle, and is basing the above statement on his experience with some other database (DB2 perhaps?). There is no problem with using sequence numbers in a RAC. No conflicts will occur. I've never heard of a problem in that regard. 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: Muqthar Ahmed 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
Re[2]: Sequences in OPS/RAC
Jonathan, Here's the text of the article [I can't find it on the WebSite, it is in the regular Oracle emails that I receive from Builder.Com] Understand SYS_GUID and sequences as primary keys Oracle8i introduced the concept of SYS_GUID, which had several advantages over a conventional sequence that Oracle administrators may use. A sequence generator simply creates a series of integer values from a given starting point and increments that series automatically whenever it's used in a select statement. Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts. An identifier created by SYS_GUID is guaranteed to be unique for each database. Moreover, a sequence has to be part of a DML statement and, therefore, requires a round-trip to the database (otherwise, it couldn't be sure that its value was unique). A SYS_GUID is derived from timestamps and machine identifiers that don't require trips to the database, saving query overhead. create table use_seq_table(id integer); create sequence use_seq_sequence; insert into use_seq_table values (use_seq_sequence_value.nextval); REM - for some reason, the documentation uses raw(32) create table use_guid_table(id raw(16)); insert into use_guid_table(sys_guid()); Many applications depend on sequence generators to create a primary key for rows that don't have an obvious primary value, namely a dataset where any of the columns could change once a record is created. Thus, admins might be tempted to use SYS_GUID as a primary key on a table instead of using sequence numbers. This works well in situations where objects are generated in different databases on separate machines and need to be merged back together later. However, the value generated by SYS_GUID is a 16-byte raw value. The integer generated by a sequence won't use 16 bytes until it gets to 10 to the 30th power (two digits per byte), and only if the digits are fairly unique: SQL> select dump(123456789012345678901234567890) from dual; DUMP(123456789012345678901234567890) -- Typ=2 Len=16: 207,13,35,57,79,91,13,35,57,79,91,13,35,57,79,91 Shorter values mean less storage space for the table and the index, as well as faster lookup access. Using either SYS_GUID or a sequence will create performance overhead somewhere in the database use cycle; it's just a question of where. For SYS_GUID, the performance hit is during query time and creation time (creating more blocks in the table and index to hold the data). For sequences, the performance hit is during the query, when the SGA sequence cache is used up. By default, a sequence caches 20 values at a time. If the database is shut down without using those values, they will be lost. Another obvious disadvantage to SYS_GUID-generated values is that it becomes much more difficult to manage values, either typing them in or populating them through scripts, or passing them as Web parameters. For these reasons, SYS_GUID might not be such a good idea to use as a primary key except in parallel environments or where it's desirable to avoid managing sequence generators. Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. {Tim [Gorman], with your permission } : These were Tim's comments when I forrwarded the article to him : This article is incredible is so many ways! First of all, the author asserts the fallacy that SEQUENCE.NEXTVAL is not unique across clustered databases, citing that sequences"simply creates a series of integer values from a given starting point and increments that series automatically", without giving thought as to how that "automatic increment" is performed. I am pretty certain that he is not confusing "distributed" with "clustered", but is in fact unclear on the basic concepts altogether, based on the gaps in his "explanation". Then, he cites that the SYS_GUID function is "derived from timestamps and machine identifiers that don't require trips to the database, saving query overhead", but he fails to mention that in order to call the function, you have to be connected to the database and issue a query or stored procedure call. I wonder how much "savings" this entails... :-) Hemant At 09:04 AM 03-11-03 -0800, you wrote: Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC> However, the Builder.Com article quite explicity asserts HKC> "Sequence generator numbers are guaranteed to be unique only for a single HKC> instance, which is unsuitable for use as a primary key in parallel or HKC> remote environments, where a sequence in each environment might gen
RE: Re[2]: Sequences in OPS/RAC
All, Just wanted to point out that 'missing' invoice numbers caused by a variety of causes (even if they were not cached), can cause problems for Accounting/Finance Depts in certain countries. Basically, the Govt looks on this as being used for 'tax avoidance', unless proved otherwise. You *can* miss uncached sequences under certain conditions when the Db restarts or a short burst of SQL causes pressure on the DD cache... Had this occur once in an Apps database and had to apply patches to undo and put back the sequence... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: Monday, November 03, 2003 10:29 AM >To: Multiple recipients of list ORACLE-L >Subject: RE: Re[2]: Sequences in OPS/RAC > > >The problem is that the ORDER clause comes at the expense of >CACHE. You can use SQL tracing to verify that each use of >the sequence causes an update of SYS.SEQ$ when ORDER is set, >effectively rendering the CACHE setting a no-op. So, >especially in an OPS/RAC environment, the use of ORDERED >sequences, especially heavily used ORDERED sequences, comes >at a steep price. > >Think about it: is ORDERED *really* necessary? In some >situations (i.e. check numbers), the ORDERED clause would be >necessary, but unless you are pumping out thousands of >checks an hour, perhaps a cached sequence shouldn't be used. > But for system-generated keys, surrogate keys, etc, I don't >think the semantics of ORDERED are necessary at all. > > > >> Hi, >> >> I have RAC and I always use ORDER when I create SEQUENCE. >> The following information is from Oracle Manual: >> ORDER is necessary only to guarantee ordered generation if >> you are using Oracle with Real Application Clusters. If >> you are using exclusive mode, sequence numbers are always >> generated in order. >> Muqthar Ahmed >> >> -Original Message- >> Sent: Monday, November 03, 2003 12:04 PM >> To: Multiple recipients of list ORACLE-L >> >> >> Hello Hemant, >> >> Monday, November 3, 2003, 11:29:26 AM, you wrote: >> HKC> However, the Builder.Com article quite explicity >> asserts HKC> "Sequence generator numbers are guaranteed to >> be unique only for a single HKC> instance, which is >> unsuitable for use as a primary key in parallel or HKC> >> remote environments, where a sequence in each environment >> might generate HKC> the same number and result in >> conflicts >> Can you point us to the article? My guess is that the >> author is not familiar with Oracle, and is basing the >> above statement on his experience with some other database >> (DB2 perhaps?). There is no problem with using sequence >> numbers in a RAC. No conflicts will occur. I've never >> heard of a problem in that regard. >> >> 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: Muqthar Ahmed >> INET: [EMAIL PROTECTED] >> >> Fat City Network Services-- 858-538-5051 >> http://www.fatcity.com San Diego, California-- >> Mailing list and web hosting services >>
RE: Re[2]: Sequences in OPS/RAC
The problem is that the ORDER clause comes at the expense of CACHE. You can use SQL tracing to verify that each use of the sequence causes an update of SYS.SEQ$ when ORDER is set, effectively rendering the CACHE setting a no-op. So, especially in an OPS/RAC environment, the use of ORDERED sequences, especially heavily used ORDERED sequences, comes at a steep price. Think about it: is ORDERED *really* necessary? In some situations (i.e. check numbers), the ORDERED clause would be necessary, but unless you are pumping out thousands of checks an hour, perhaps a cached sequence shouldn't be used. But for system-generated keys, surrogate keys, etc, I don't think the semantics of ORDERED are necessary at all. > Hi, > > I have RAC and I always use ORDER when I create SEQUENCE. > The following information is from Oracle Manual: > ORDER is necessary only to guarantee ordered generation if > you are using Oracle with Real Application Clusters. If > you are using exclusive mode, sequence numbers are always > generated in order. > Muqthar Ahmed > > -Original Message- > Sent: Monday, November 03, 2003 12:04 PM > To: Multiple recipients of list ORACLE-L > > > Hello Hemant, > > Monday, November 3, 2003, 11:29:26 AM, you wrote: > HKC> However, the Builder.Com article quite explicity > asserts HKC> "Sequence generator numbers are guaranteed to > be unique only for a single HKC> instance, which is > unsuitable for use as a primary key in parallel or HKC> > remote environments, where a sequence in each environment > might generate HKC> the same number and result in > conflicts > Can you point us to the article? My guess is that the > author is not familiar with Oracle, and is basing the > above statement on his experience with some other database > (DB2 perhaps?). There is no problem with using sequence > numbers in a RAC. No conflicts will occur. I've never > heard of a problem in that regard. > > 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: Muqthar Ahmed > 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: 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).
RE: Re[2]: Sequences in OPS/RAC
Hi, I have RAC and I always use ORDER when I create SEQUENCE. The following information is from Oracle Manual: ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order. Muqthar Ahmed -Original Message- Sent: Monday, November 03, 2003 12:04 PM To: Multiple recipients of list ORACLE-L Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC> However, the Builder.Com article quite explicity asserts HKC> "Sequence generator numbers are guaranteed to be unique only for a single HKC> instance, which is unsuitable for use as a primary key in parallel or HKC> remote environments, where a sequence in each environment might generate HKC> the same number and result in conflicts Can you point us to the article? My guess is that the author is not familiar with Oracle, and is basing the above statement on his experience with some other database (DB2 perhaps?). There is no problem with using sequence numbers in a RAC. No conflicts will occur. I've never heard of a problem in that regard. 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: Muqthar Ahmed 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).
Re: Re[2]: Sequences in OPS/RAC
On 11/03/2003 12:04:26 PM, Jonathan Gennick wrote: > > Can you point us to the article? My guess is that the author > is not familiar with Oracle, That shouldn't be considered enough of a reason not to write articles about oracle, should it? 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).
Re[2]: Sequences in OPS/RAC
Hello Hemant, Monday, November 3, 2003, 11:29:26 AM, you wrote: HKC> However, the Builder.Com article quite explicity asserts HKC> "Sequence generator numbers are guaranteed to be unique only for a single HKC> instance, which is unsuitable for use as a primary key in parallel or HKC> remote environments, where a sequence in each environment might generate HKC> the same number and result in conflicts Can you point us to the article? My guess is that the author is not familiar with Oracle, and is basing the above statement on his experience with some other database (DB2 perhaps?). There is no problem with using sequence numbers in a RAC. No conflicts will occur. I've never heard of a problem in that regard. 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).
Re: Sequences in OPS/RAC
Yes, I've been aware of the difference between ORDERED and CACHED. However, the Builder.Com article quite explicity asserts "Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts" As Tim has pointed out earlier, the author of the article might have confused uniqueness with ordering. {I've sent copies of the builder.com article by seperate emails to Raj and Tim} Hemant At 07:59 AM 03-11-03 -0800, you wrote: In the "Oracle9i Real Application Clusters Administration" manual, there is a chapter about sequence numbers generator. Before believing to the snake oil sellers, read the fine manual. Sequence numbers are guaranteed to be unique PER DATABASE. What they're not guaranteed is to come in ordered fashion. When sequence number are cached (that is the default), they're cached separately, for each instance. Each instance returns the contents of its cache, so it is possible for the smaller number being returned after a larger one. There is "ORDERED" flag to deal with that, but that can be extremely expensive and impose significant overhead on your cluster. On 11/03/2003 10:39:26 AM, Hemant K Chitale wrote: > > > I have always been comfortable with the idea that Sequences continue to > guarantee > uniqueness even in OPS / RAC environments. > > However, a recent Builder.Com article by Scott Stephens on the SYS_GUID > function has these lines : > "Sequence generator numbers are guaranteed to be unique only for a single > instance, which is unsuitable for use as a primary key in parallel or > remote environments, where a sequence in each environment might generate > the same number and result in conflicts. An identifier created by SYS_GUID > is guaranteed to be unique for each database." > > Huh ?! Do the lines mean that a single sequence can have duplicate values > in the two instances of an RAC cluster ? > > > Hemant K Chitale > Oracle 9i Database Administrator Certified Professional > My personal web site is : http://hkchital.tripod.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hemant K Chitale > 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the offici
Re: Sequences in OPS/RAC
Sequences are mastered by the single SYS.SEQ$ table in each database. Cached or uncached, RAC or non-RAC, OPS or non-OPS, sequence numbers generated by this mechanism are unique across a database, not by instance. Each instance updates SEQ$ as individual numbers (noncached) or ranges of numbers (cached) are "reserved", and those updates are controlled by the same synchronization mechanisms used by OPS/RAC for all UPDATE statements. This is precisely the reason that setting CACHE on sequence numbers help performance, as the number of updates to SEQ$ are reduced, minimizing the bottleneck. However, because of the simplicity of this caching mechanism, sequence numbers are not guaranteed to be in order (i.e. sequentially ascending) across multiple instances. Perhaps Mr. Stephens mis-spoke, confusing uniqueness for ordering? Or perhaps he is confusing "distributed databases" for "clustered databases"? > > > I have always been comfortable with the idea that > Sequences continue to guarantee > uniqueness even in OPS / RAC environments. > > However, a recent Builder.Com article by Scott Stephens on > the SYS_GUID function has these lines : > "Sequence generator numbers are guaranteed to be unique > only for a single instance, which is unsuitable for use > as a primary key in parallel or remote environments, > where a sequence in each environment might generate the > same number and result in conflicts. An identifier created > by SYS_GUID is guaranteed to be unique for each > database." > Huh ?! Do the lines mean that a single sequence can have > duplicate values in the two instances of an RAC cluster ? > > > Hemant K Chitale > Oracle 9i Database Administrator Certified Professional > My personal web site is : http://hkchital.tripod.com > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: Hemant K Chitale > 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: Tim Gorman 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).
Re: Sequences in OPS/RAC
In the "Oracle9i Real Application Clusters Administration" manual, there is a chapter about sequence numbers generator. Before believing to the snake oil sellers, read the fine manual. Sequence numbers are guaranteed to be unique PER DATABASE. What they're not guaranteed is to come in ordered fashion. When sequence number are cached (that is the default), they're cached separately, for each instance. Each instance returns the contents of its cache, so it is possible for the smaller number being returned after a larger one. There is "ORDERED" flag to deal with that, but that can be extremely expensive and impose significant overhead on your cluster. On 11/03/2003 10:39:26 AM, Hemant K Chitale wrote: > > > I have always been comfortable with the idea that Sequences continue to > guarantee > uniqueness even in OPS / RAC environments. > > However, a recent Builder.Com article by Scott Stephens on the SYS_GUID > function has these lines : > "Sequence generator numbers are guaranteed to be unique only for a single > instance, which is unsuitable for use as a primary key in parallel or > remote environments, where a sequence in each environment might generate > the same number and result in conflicts. An identifier created by SYS_GUID > is guaranteed to be unique for each database." > > Huh ?! Do the lines mean that a single sequence can have duplicate values > in the two instances of an RAC cluster ? > > > Hemant K Chitale > Oracle 9i Database Administrator Certified Professional > My personal web site is : http://hkchital.tripod.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Hemant K Chitale > 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).
RE: Sequences in OPS/RAC
me don't think so. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, November 03, 2003 10:39 AM To: Multiple recipients of list ORACLE-L I have always been comfortable with the idea that Sequences continue to guarantee uniqueness even in OPS / RAC environments. However, a recent Builder.Com article by Scott Stephens on the SYS_GUID function has these lines : "Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts. An identifier created by SYS_GUID is guaranteed to be unique for each database." Huh ?! Do the lines mean that a single sequence can have duplicate values in the two instances of an RAC cluster ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Sequences in OPS/RAC
Hemant, I would guess that this is true if you are caching values for the sequence. Each database instance might cache the same set of values. Turn sequence caching off, and I would think that the problem goes away. Havn't tried this in awhile, but it makes sense. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, November 03, 2003 10:39 AM To: Multiple recipients of list ORACLE-L I have always been comfortable with the idea that Sequences continue to guarantee uniqueness even in OPS / RAC environments. However, a recent Builder.Com article by Scott Stephens on the SYS_GUID function has these lines : "Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts. An identifier created by SYS_GUID is guaranteed to be unique for each database." Huh ?! Do the lines mean that a single sequence can have duplicate values in the two instances of an RAC cluster ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Mercadante, Thomas F 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).
Sequences in OPS/RAC
I have always been comfortable with the idea that Sequences continue to guarantee uniqueness even in OPS / RAC environments. However, a recent Builder.Com article by Scott Stephens on the SYS_GUID function has these lines : "Sequence generator numbers are guaranteed to be unique only for a single instance, which is unsuitable for use as a primary key in parallel or remote environments, where a sequence in each environment might generate the same number and result in conflicts. An identifier created by SYS_GUID is guaranteed to be unique for each database." Huh ?! Do the lines mean that a single sequence can have duplicate values in the two instances of an RAC cluster ? Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
RE: [PMX:#] RE: Find the table's name that using sequences
Title: RE: [PMX:#] RE: Find the table's name that using sequences Very True ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 16, 2003 5:25 PM To: Multiple recipients of list ORACLE-L Subject: [PMX:#] RE: Find the table's name that using sequences this doesn't take into account external code that uses the sequence there is no way to know for certain which sequence is being used to generate values for which table. Even if you have multiple sequences, you can't force a programmer to use the sequence you have designated as "the one" for that particular table --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > I'd check dependencies of the sequence, you'll know what > procedures,functions,packages,triggers that sue the sequence. > > Raj This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Find the table's name that using sequences
Oh yeah. I forgot about that LONG data type pain in the anus when you are looking for something. I just attended 9i new features in Colorado Springs last week. I asked, with all these new features, why the implementation of the LONG data type was still so piss poor (well, maybe not exactly in those terms, but that sentiment). I didn't get any answer. But the weather there was lovely. No humidity at all (unlike the steam bath here in Tulsa). It was bone dry, but that's their problem. I enjoyed the weather before heading back to green grass and night air filled with lighting bugs, the ratcheting sound of cicadas, and humidity ... lots and lots of humidity ... hot humidity. Click your heals together three times and repeat after me: At least you don't live in Houston. At least you don't live ... Yet another long shot is to go plowing through V$SQL or V$SQLTEXT_WITH_NEWLINES which gets my vote for world's worst view. When a view makes me say screw it and resort to a GUI, then that's BAD. If all of this searching produces nothing, I suppose you can periodically check the value of the sequence to see if it is going up. Maybe note the current value of the sequence and grants on it, then drop it to see if anything goes invalid?? (or if anyone hollers) Are we feeling bold and daring? > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 16, 2003 7:39 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Find the table's name that using sequences > > > That won't catch sequences in triggers. > > You can't easily find sequence use in a trigger either, as > the code is stored in a LONG. > > Best to dump to a text file and use grep. > > And if your programmers practice safe sequences, it will be > in their code instead of the database anyway. > > Although if they're *really* good, it will be in a package, and > it *will* show up in dba_source. > > Jared > > > > > > Stephen Lee <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 07/16/2003 02:14 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:RE: Find the table's name that using sequences > > > > A mad rampage through DBA_SOURCE might reveal something useful. > Something like: > select name,text from dba_source where upper(text) like > '%SEQUENCE_NAME%'; > > And do the same with TRIGGER_BODY from DBA_TRIGGERS. > > -Original Message- > -- > -- > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> > Hi All > > At first I thought it is easy to find those tables to use > sequences but I > failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Lee > 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: > 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: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: Find the table's name that using sequences
Chris, The system I work on currently has a main sequence issuing GUID's (Globally Unique Identifier's) for the entire application. I think it comes from the Object Orientated world - where some physical tables may be storing various different logical records and sometimes a foreign key may point to a different table based on the contents of yet another field. Do the id's need to be globally unique - probably not in my opinion but I guess it also stops some silly mistakes from occuring - you can't accidentally select a record from the wrong table. Our system is sitting ~1 TB of data from Oracle's point of view and probably around a couple of billion records between the main tables. On any given day many million records would be created, but I have never tried to calculate the figure. There are a few ways to mitigate performance problems: 1) Set some caching on the sequence. 2) The sequence in Oracle represents a block of 1,000. So, if Oracle says the sequence is 2134 then it has really given a block of GUIDs from 2134000 to 2134999. Different processes within the application can therefore grab a value from the sequence and perform 1,000 inserts before requesting another value - no other process can ever get that block of 1,000. 3) Sure there would be some missing values - gaps where the application was shutdown with blocks only partially used. Scripts which had to grab an entire block but only insert 10 rows, etc. Gaps aren't a problem and if you do the mathematics the number of values available is quite high. Our sequence is currently at 8450712 - issuing GUID's between 8,450,712,000 and 8,450,712,999. 4) GUID's are stored in a 20-character column so if we ever run out of space to store numbers I guess they could add a character (like the letter A) - although that isn't in the plan. Cheers, Mark. Chris Grabowy <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> co.com> cc: Sent by: Subject: RE: Find the table's name that using sequences [EMAIL PROTECTED] .com 17/07/2003 06:29 Please respond to ORACLE-L Well, there could be business logic reasons as to why you would have one sequence per table. Also, I don't know if I would ever go with one sequence for many tables, sounds like a bottle neck to me. And how would one sequence for many tables impact scalability?? Or having lots of users hammering the database?? And what happens if you have to reset the sequence, then you have to check the primary key values on many tables. One sequence to one table sounds good to me, but I would love to hear pros/cons about this... -Original Message- Sent: Wednesday, July 16, 2003 3:35 PM To: Multiple recipients of list ORACLE-L no table "uses" a sequence. And there is no reason (other than sanity checks) to have one sequence per table. SQL code will use the sequence, usually to retrieve a value from the sequence to then insert into or update a column in a table. --- Mitchell <[EMAIL PROTECTED]> wrote: > Hi All > > At first I thought it is easy to find those tables to use sequences > but > I failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > _
RE: Find the table's name that using sequences
That won't catch sequences in triggers. You can't easily find sequence use in a trigger either, as the code is stored in a LONG. Best to dump to a text file and use grep. And if your programmers practice safe sequences, it will be in their code instead of the database anyway. Although if they're *really* good, it will be in a package, and it *will* show up in dba_source. Jared Stephen Lee <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/16/2003 02:14 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Find the table's name that using sequences A mad rampage through DBA_SOURCE might reveal something useful. Something like: select name,text from dba_source where upper(text) like '%SEQUENCE_NAME%'; And do the same with TRIGGER_BODY from DBA_TRIGGERS. -Original Message- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: 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).
Re: Find the table's name that using sequences
In addition to what Jacques has mentioned, here is my 0.02. Why only one sequence per table? Does it stem from the concern that a single sequence becomes "overloaded" with request to be inserted into multiple tables? The overloading does not come from number of tables, but number of concurrent requests, which perhaps more indicated by the number of users in the system at any point in time, regardless of how many tables. If you have a single sequence serving PKs of 500 tables with an average of 1 concurrent user, is that worse than 500 concurrent users and 1 sequence per table? The load on the seqeunce will still be the same. So performance is not the right reason to look at this issue. The correct reson is business. If you have 2 tables getting the their PK value from the same sequence, you will have "gaps" in the PK as each table will grab values from the sequence. Is that acceptable? If the answer is no, you shouldn't even consider sequences; they are _bound_ to have gaps. Using a independent sequence for a table sometimes makes sense to retrieve the last used number and guess the next PK value to be generated on that table. A sequence per table will allow that, multiple tables will not. Another factor to use a single sequence for a table's PK is, as Rachel mentioned, sanity check. Our developers (encouraged_ by yours truly!) use a sequence per table and follow the naming convention as SEQ_, just as an easy reference to the table. sometimes, it is required to use the same sequence number for two tables; the name then becomes SEQ__ and so on. HTH. Arup Nanda - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, July 16, 2003 4:39 PM > If you are using the sequence to generate the primary key for a table, then the sequence should only be used for that table. I can't think of a pro to have one sequence shared for the primary keys on many different tables. > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > > Chris Grabowy > > > > Well, there could be business logic reasons as to why you > > would have one > > sequence per table. > > > > Also, I don't know if I would ever go with one sequence for > > many tables, > > sounds like a bottle neck to me. And how would one sequence > > for many tables > > impact scalability?? Or having lots of users hammering the > > database?? And > > what happens if you have to reset the sequence, then you have > > to check the > > primary key values on many tables. One sequence to one table > > sounds good to > > me, but I would love to hear pros/cons about this... > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jacques Kilchoer > 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: Arup Nanda 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).
RE: Find the table's name that using sequences
this doesn't take into account external code that uses the sequence there is no way to know for certain which sequence is being used to generate values for which table. Even if you have multiple sequences, you can't force a programmer to use the sequence you have designated as "the one" for that particular table --- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote: > I'd check dependencies of the sequence, you'll know what > procedures,functions,packages,triggers that sue the sequence. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> > Hi All > > At first I thought it is easy to find those tables to use sequences > but I > failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > > This > e-mail message is confidential, intended only for the named > recipient(s) above and may contain information that is privileged, > attorney work product or exempt from disclosure under applicable law. > If you have received this message in error, or are not the named > recipient(s), please immediately notify corporate MIS at (860) > 766-2000 and delete this e-mail message from your computer, Thank > you.*2 > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
RE: Find the table's name that using sequences
A mad rampage through DBA_SOURCE might reveal something useful. Something like: select name,text from dba_source where upper(text) like '%SEQUENCE_NAME%'; And do the same with TRIGGER_BODY from DBA_TRIGGERS. -Original Message- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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).
RE: Find the table's name that using sequences
In order for sequence not to become a "bottle neck", use "cache" option. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 16, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Well, there could be business logic reasons as to why you would have one sequence per table. Also, I don't know if I would ever go with one sequence for many tables, sounds like a bottle neck to me. And how would one sequence for many tables impact scalability?? Or having lots of users hammering the database?? And what happens if you have to reset the sequence, then you have to check the primary key values on many tables. One sequence to one table sounds good to me, but I would love to hear pros/cons about this... -Original Message- Sent: Wednesday, July 16, 2003 3:35 PM To: Multiple recipients of list ORACLE-L no table "uses" a sequence. And there is no reason (other than sanity checks) to have one sequence per table. SQL code will use the sequence, usually to retrieve a value from the sequence to then insert into or update a column in a table. --- Mitchell <[EMAIL PROTECTED]> wrote: > Hi All > > At first I thought it is easy to find those tables to use sequences > but > I failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com <http://sbc.yahoo.com> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com <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: Igor Neyman 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).
RE: Find the table's name that using sequences
Title: RE: Find the table's name that using sequences I'd check dependencies of the sequence, you'll know what procedures,functions,packages,triggers that sue the sequence. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Find the table's name that using sequences
I didn't say you should have only one sequence in the database. I said there was no reason you had to have multiple ones. and there isn't. There is no restriction in Oracle that you have to do so. As it happens, for many of the reasons you stated, we have multiple sequences. That is, where I can get the developers to use sequences. they use a development package that is object-oriented. And which therefore stores a row in a single table for every table for which they are generating a sequence number. so if they have 100 tables, I have 100 rows in a single Oracle table. It's only slightly better than having one row in a table as the "sequence". I'd take the performance of a single Oracle sequence over the performance of the table with multiple rows any day. --- Chris Grabowy <[EMAIL PROTECTED]> wrote: > Well, there could be business logic reasons as to why you would have > one > sequence per table. > > Also, I don't know if I would ever go with one sequence for many > tables, > sounds like a bottle neck to me. And how would one sequence for many > tables > impact scalability?? Or having lots of users hammering the > database?? And > what happens if you have to reset the sequence, then you have to > check the > primary key values on many tables. One sequence to one table sounds > good to > me, but I would love to hear pros/cons about this... > > -Original Message- > Sent: Wednesday, July 16, 2003 3:35 PM > To: Multiple recipients of list ORACLE-L > > > > no table "uses" a sequence. And there is no reason (other than sanity > > checks) to have one sequence per table. > > SQL code will use the sequence, usually to retrieve a value from the > sequence to then insert into or update a column in a table. > > > --- Mitchell <[EMAIL PROTECTED]> wrote: > > Hi All > > > > At first I thought it is easy to find those tables to use sequences > > > but > > I failed. dba_sequence don't give too much info. Is there any > idea? > > > > Thanks in advance > > Mitchell > > > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com <http://sbc.yahoo.com> > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > <http://www.orafaq.net> > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > <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). > > > ATTACHMENT part 2 application/ms-tnef name=winmail.dat __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
RE: Find the table's name that using sequences
If you are using the sequence to generate the primary key for a table, then the sequence should only be used for that table. I can't think of a pro to have one sequence shared for the primary keys on many different tables. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Chris Grabowy > > Well, there could be business logic reasons as to why you > would have one > sequence per table. > > Also, I don't know if I would ever go with one sequence for > many tables, > sounds like a bottle neck to me. And how would one sequence > for many tables > impact scalability?? Or having lots of users hammering the > database?? And > what happens if you have to reset the sequence, then you have > to check the > primary key values on many tables. One sequence to one table > sounds good to > me, but I would love to hear pros/cons about this... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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).
RE: Find the table's name that using sequences
Well, there could be business logic reasons as to why you would have one sequence per table. Also, I don't know if I would ever go with one sequence for many tables, sounds like a bottle neck to me. And how would one sequence for many tables impact scalability?? Or having lots of users hammering the database?? And what happens if you have to reset the sequence, then you have to check the primary key values on many tables. One sequence to one table sounds good to me, but I would love to hear pros/cons about this... -Original Message- Sent: Wednesday, July 16, 2003 3:35 PM To: Multiple recipients of list ORACLE-L no table "uses" a sequence. And there is no reason (other than sanity checks) to have one sequence per table. SQL code will use the sequence, usually to retrieve a value from the sequence to then insert into or update a column in a table. --- Mitchell <[EMAIL PROTECTED]> wrote: > Hi All > > At first I thought it is easy to find those tables to use sequences > but > I failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com <http://sbc.yahoo.com> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com <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). <>
Re: Find the table's name that using sequences
Michell, A sequence is created and select privileges granted to users or roles. The users or roles use the sequence in the applications to insert/update the data in the tables. Check the privileges granted to the users/roles and then get the info from the developers as to which tables use the sequence. A daunting task is in your future if you do not have your database documented. Good luck, Ron >>> [EMAIL PROTECTED] 07/16/03 03:09PM >>> Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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).
RE: Find the table's name that using sequences
Title: Message Sequence is not associated with any particular table. One sequence could be used to populate multiple tables. If you know, that sequences are being used in “insert” triggers only (and not in the application outside the database), then you could search for sequence name in trigger_body of dba_triggers table. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mitchell Sent: Wednesday, July 16, 2003 2:09 PM To: Multiple recipients of list ORACLE-L Subject: Find the table's name that using sequences Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell
Re: Find the table's name that using sequences
no table "uses" a sequence. And there is no reason (other than sanity checks) to have one sequence per table. SQL code will use the sequence, usually to retrieve a value from the sequence to then insert into or update a column in a table. --- Mitchell <[EMAIL PROTECTED]> wrote: > Hi All > > At first I thought it is easy to find those tables to use sequences > but > I failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
Find the table's name that using sequences
Title: Message Hi All At first I thought it is easy to find those tables to use sequences but I failed. dba_sequence don't give too much info. Is there any idea? Thanks in advance Mitchell
RE: Replicating Sequences...
1. - No support in replication for replication of sequences that I'm aware of. This is one of the shortcomings of replication (among other things). 2. How about running some test queries that you know baseline response times every four hours and monitoring the response time and alerting if they bust a set threshold. RF Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 904.708.5076 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message- Sent: Tuesday, March 25, 2003 9:45 AM To: Multiple recipients of list ORACLE-L Hi to everybody! I would like to get a bit of help with: 1.- Can I replicate sequences on a Master to Master site? What are the implications on this? 2.- I'm going to have a Master to Master replication site and it's going to have 4 hours of INTENSIVE insertion of data DAILY and, of course, query of data, the question is: How can I measure the response times on that 4 hours? I would like to be able to calculate an aproximate response times! Any ideas? Thanks in advance! JL __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Freeman Robert - IL 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).
Replicating Sequences...
Hi to everybody! I would like to get a bit of help with: 1.- Can I replicate sequences on a Master to Master site? What are the implications on this? 2.- I'm going to have a Master to Master replication site and it's going to have 4 hours of INTENSIVE insertion of data DAILY and, of course, query of data, the question is: How can I measure the response times on that 4 hours? I would like to be able to calculate an aproximate response times! Any ideas? Thanks in advance! JL __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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).
RE: Sequences in 8.1.7 vs 9i
Daniel: You are a sick person. Not going to change the code that much. I need to evaluate the pain for the development team. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 23, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Subject:RE: Sequences in 8.1.7 vs 9i Stephen, Wild-hair idea...could you create a function with the name nextval and use it to populate from the sequence? Dan Fink -Original Message- Sent: Thursday, January 23, 2003 6:49 AM To: Multiple recipients of list ORACLE-L Yes. I agree that works. The problem is that there are over 2000 lines of code similar to the one I identified. I'm not interested in recommending changing all of it unless it's a conversion issue. Thanks for the help. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 23, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Sequences in 8.1.7 vs 9i Stephen, What about this, SQL > Create sequence a; SQL > Create table xxx(numtest number, testvalue varchar2(100)); SQL > declare x number:=0; begin select a.nextval into x from dual; Insert into xxx values(x, 'TEST'); end; Rgrd, Sony > -Original Message- > From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 4:49 AM > To: Multiple recipients of list ORACLE-L > Subject: Sequences in 8.1.7 vs 9i > > Hello everyone. > > This one stumps me and I'm wondering if it is a bug that was resolved in > 9i. Here is sample code. > > Create sequence a; > Create table xxx(numtest number, testvalue varchar2(100)); > > Inside PL/SQL block and from SQL*Plus Prompt; > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > PL/SQL: ORA-02287: sequence number not allowed here > > Take out the () after the sequence name and all is well. Anyone > experience > something like this??? > Application (not my code) written in 8i but imported the database into 9i. > > > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email:[EMAIL PROTECTED] > Web: www.compuware.com > > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or > disclose > it to anyone else. If you received it in error please notify us > immediately > and then destroy it. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > 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: Sony kristanto 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Servic
RE: Sequences in 8.1.7 vs 9i
sed will fix that in a big hurry. PERL?! We don't need no stinkin' perl! > -Original Message- > > The problem is that there are over > 2000 lines of > code similar to the one I identified. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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).
RE: Sequences in 8.1.7 vs 9i
Stephen, Wild-hair idea...could you create a function with the name nextval and use it to populate from the sequence? Dan Fink -Original Message- Sent: Thursday, January 23, 2003 6:49 AM To: Multiple recipients of list ORACLE-L Yes. I agree that works. The problem is that there are over 2000 lines of code similar to the one I identified. I'm not interested in recommending changing all of it unless it's a conversion issue. Thanks for the help. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 23, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Sequences in 8.1.7 vs 9i Stephen, What about this, SQL > Create sequence a; SQL > Create table xxx(numtest number, testvalue varchar2(100)); SQL > declare x number:=0; begin select a.nextval into x from dual; Insert into xxx values(x, 'TEST'); end; Rgrd, Sony > -Original Message- > From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 4:49 AM > To: Multiple recipients of list ORACLE-L > Subject: Sequences in 8.1.7 vs 9i > > Hello everyone. > > This one stumps me and I'm wondering if it is a bug that was resolved in > 9i. Here is sample code. > > Create sequence a; > Create table xxx(numtest number, testvalue varchar2(100)); > > Inside PL/SQL block and from SQL*Plus Prompt; > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > PL/SQL: ORA-02287: sequence number not allowed here > > Take out the () after the sequence name and all is well. Anyone > experience > something like this??? > Application (not my code) written in 8i but imported the database into 9i. > > > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email:[EMAIL PROTECTED] > Web: www.compuware.com > > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or > disclose > it to anyone else. If you received it in error please notify us > immediately > and then destroy it. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > 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: Sony kristanto 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen 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 sen
RE: Sequences in 8.1.7 vs 9i
Yes. I agree that works. The problem is that there are over 2000 lines of code similar to the one I identified. I'm not interested in recommending changing all of it unless it's a conversion issue. Thanks for the help. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 23, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Subject: RE: Sequences in 8.1.7 vs 9i Stephen, What about this, SQL > Create sequence a; SQL > Create table xxx(numtest number, testvalue varchar2(100)); SQL > declare x number:=0; begin select a.nextval into x from dual; Insert into xxx values(x, 'TEST'); end; Rgrd, Sony > -Original Message- > From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 4:49 AM > To: Multiple recipients of list ORACLE-L > Subject: Sequences in 8.1.7 vs 9i > > Hello everyone. > > This one stumps me and I'm wondering if it is a bug that was resolved in > 9i. Here is sample code. > > Create sequence a; > Create table xxx(numtest number, testvalue varchar2(100)); > > Inside PL/SQL block and from SQL*Plus Prompt; > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > PL/SQL: ORA-02287: sequence number not allowed here > > Take out the () after the sequence name and all is well. Anyone > experience > something like this??? > Application (not my code) written in 8i but imported the database into 9i. > > > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email:[EMAIL PROTECTED] > Web: www.compuware.com > > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or > disclose > it to anyone else. If you received it in error please notify us > immediately > and then destroy it. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > 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: Sony kristanto 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen 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).
Re: Sequences in 8.1.7 vs 9i
Steven, m.b. this is dumb question, but did you check, that there is no function called "nextval" inside user-written package called "a", which hides actual retrieval of sequence next value? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 23, 2003 8:49 AM > Yes. I agree that works. The problem is that there are over 2000 lines of > code similar to the one I identified. I'm not interested in recommending > changing all of it unless it's a conversion issue. > > Thanks for the help. > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email: [EMAIL PROTECTED] > Web: www.compuware.com > > -Original Message- > Sent: Thursday, January 23, 2003 2:54 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Sequences in 8.1.7 vs 9i > > Stephen, > > What about this, > SQL > Create sequence a; > SQL > Create table xxx(numtest number, testvalue varchar2(100)); > SQL > declare > x number:=0; > begin > select a.nextval into x from dual; > Insert into xxx values(x, 'TEST'); > end; > > Rgrd, > > Sony > > > -----Original Message- > > From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, January 23, 2003 4:49 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Sequences in 8.1.7 vs 9i > > > > Hello everyone. > > > > This one stumps me and I'm wondering if it is a bug that was resolved in > > 9i. Here is sample code. > > > > Create sequence a; > > Create table xxx(numtest number, testvalue varchar2(100)); > > > > Inside PL/SQL block and from SQL*Plus Prompt; > > > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > > > PL/SQL: ORA-02287: sequence number not allowed here > > > > Take out the () after the sequence name and all is well. Anyone > > experience > > something like this??? > > Application (not my code) written in 8i but imported the database into 9i. > > > > > > > > Thank You > > > > Stephen P. Karniotis > > Product Architect > > Compuware Corporation > > Direct: (248) 865-4350 > > Mobile: (248) 408-2918 > > Email: [EMAIL PROTECTED] > > Web: www.compuware.com > > > > > > > > > > The contents of this e-mail are intended for the named addressee only. It > > contains information that may be confidential. Unless you are the named > > addressee or an authorized designee, you may not copy or use it, or > > disclose > > it to anyone else. If you received it in error please notify us > > immediately > > and then destroy it. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Karniotis, Stephen > > 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: Sony kristanto > 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). > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or disclose > it to anyone else. If you rece
RE: Sequences in 8.1.7 vs 9i
Stephen, What about this, SQL > Create sequence a; SQL > Create table xxx(numtest number, testvalue varchar2(100)); SQL > declare x number:=0; begin select a.nextval into x from dual; Insert into xxx values(x, 'TEST'); end; Rgrd, Sony > -Original Message- > From: Karniotis, Stephen [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 4:49 AM > To: Multiple recipients of list ORACLE-L > Subject: Sequences in 8.1.7 vs 9i > > Hello everyone. > > This one stumps me and I'm wondering if it is a bug that was resolved in > 9i. Here is sample code. > > Create sequence a; > Create table xxx(numtest number, testvalue varchar2(100)); > > Inside PL/SQL block and from SQL*Plus Prompt; > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > PL/SQL: ORA-02287: sequence number not allowed here > > Take out the () after the sequence name and all is well. Anyone > experience > something like this??? > Application (not my code) written in 8i but imported the database into 9i. > > > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email:[EMAIL PROTECTED] > Web: www.compuware.com > > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or > disclose > it to anyone else. If you received it in error please notify us > immediately > and then destroy it. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > 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: Sony kristanto 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).
Re: Sequences in 8.1.7 vs 9i
I have never seen () after nextval in 7.3 or 8i or 9i . -Bp - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 22, 2003 1:48 PM > Hello everyone. > > This one stumps me and I'm wondering if it is a bug that was resolved in > 9i. Here is sample code. > > Create sequence a; > Create table xxx(numtest number, testvalue varchar2(100)); > > Inside PL/SQL block and from SQL*Plus Prompt; > > Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); > > PL/SQL: ORA-02287: sequence number not allowed here > > Take out the () after the sequence name and all is well. Anyone experience > something like this??? > Application (not my code) written in 8i but imported the database into 9i. > > > > Thank You > > Stephen P. Karniotis > Product Architect > Compuware Corporation > Direct: (248) 865-4350 > Mobile: (248) 408-2918 > Email: [EMAIL PROTECTED] > Web: www.compuware.com > > > > > The contents of this e-mail are intended for the named addressee only. It > contains information that may be confidential. Unless you are the named > addressee or an authorized designee, you may not copy or use it, or disclose > it to anyone else. If you received it in error please notify us immediately > and then destroy it. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Karniotis, Stephen > 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: BigP 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).
Sequences in 8.1.7 vs 9i
Hello everyone. This one stumps me and I'm wondering if it is a bug that was resolved in 9i. Here is sample code. Create sequence a; Create table xxx(numtest number, testvalue varchar2(100)); Inside PL/SQL block and from SQL*Plus Prompt; Insert into xxx(numbest, testvalue) values (a.nextval(), 'TEST'); PL/SQL: ORA-02287: sequence number not allowed here Take out the () after the sequence name and all is well. Anyone experience something like this??? Application (not my code) written in 8i but imported the database into 9i. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen 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).
RE: OPS Sequences: nocache == order ??
As I said I didn't verify his figures nor confirm he understands what constitutes a transaction. I'll endeavor to do so. The system collects data from monitors measuring the "health" of various test accelerator equipment. The telemetry is buffered before being inserted so that multiple readings could be pushed to the database as a single transaction which buoys Anjo's thought that 13,000 rows per second are being inserted not 13,000 tps. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Saturday, September 07, 2002 1:13 AM To: Multiple recipients of list ORACLE-L 1) I think that the tpc numbers are done represented in Transactons Per Minute (TPM/C) and not Per second. So event with 50 tpm/c it means around 8000 tps. 2) Inserting 13000 rows with direct I/O doesn't mean you did 13000 transactions. It could be one transaction 3) I have seen the theoretical limit, but if I recall correctly it was the number of SCN numbers that was generated. That way they can calculate how long it takes before the SCN number will wrap (it is only 48 bits). That is way in the future. Anjo. On Saturday 07 September 2002 04:08, you wrote: > One of our accelerator control system developers, an Oracle neophyte, > claims to have achieved 13,000 tps writing to a RAID 5 array. I did set up > the database, but most of the credit goes to him for exploring the OCI > direct I/O options. I have no verified the rate, but I have no reason > whatsoever to doubt him. > > This is on older four processor sun box. We've now traded in the lone > a-1000 ,attached two T3's, and turned on archive logging. I had him retest > and he said it was quicker than before . It's still RAID 5. If you are > wondering why RAID 5, we have another little 659.9 Terabyte database and > thousands of machines in compute farms to process the associated data. > That project has first choice, and the rest of us make do with what's left. > > I too am curious where this theoretical limit of 16384 comes from. > Theoretical as it no matter what hardware one chose this limit could not be > surpassed? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > > > > -Original Message- > Sent: Friday, September 06, 2002 4:38 PM > To: Multiple recipients of list ORACLE-L > > On Wednesday 04 September 2002 09:53, Tim Gorman wrote: > > Thinking more about it last night... > > > > Since Oracle's theoretical limit is 16384 commits per second, I imagine > > that you could safely make the sequence recycle at (or 16384 or > > 9) and limit the number of digits contributed by the sequence to > > 4-5... > > Really? What have they done in the past to get those astronomical TPS > numbers on some of their bencmarks? > > I'm pretty sure they were in excess of that number. > > IIRC, they were done on an nCube using OPS and about 400 CPUs. > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
Ok, It is saturday morning (brain is working at half power), I have looked at this function and at the original requirement and see a problem (may be 2). 1) The time of the multiple instances needs to be in sync with each other. If not, it could be that the 2nd instance has an earlier time and insert a record with a lower number after an insert of a higher number. So the real order is lost (that was a requirement). 2) Given the fact that the sequence numbers may be cached, even when the time is in sync, depending on the cached sequence numbers you could still end up with one instance inserting a number with a higher sequence number before the other instance with a lower sequence number in the same time (at seconds level). So if the requirements aren't so strict, why not drop the 'no order' and bump the cache ? Again, I may have missed something. Anjo. On Wednesday 04 September 2002 08:28, you wrote: > Mladen, > > Is there any way to have developers/users access the sequence via a > function, instead of accessing the sequence directly? > > If so, then perhaps you could modify the sequence to add the temporal > component, while maintaining the use of a cached sequence for uniqueness? > Such as: SQL> create or replace function gen_seqq(in_seq in number) > 2 return number > 3 as > 4 v_return_nbr number; > 5 begin > 6 select > to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'00 >00'))) 7 into v_return_nbr > 8 from dual; > 9 return v_return_nbr; >10* end gen_seqq; > SQL> / > > Function created. > > SQL> create table x (y number); > > Table created. > > SQL> create sequence xq; > > Sequence created. > > SQL> insert into x values (gen_seqq(xq.nextval)); > > 1 row created. > > SQL> > Big and ugly numbers yes, but I think some folks get a strange thrill out > of 20-digit numbers. > > It fits the requirement of being temporal (to the second, at least) and > unique. You can throw in HSECS from V$TIMER if someone gets picky enough > to want to go to the centi-second level as well. Yeah, and you can throw > in USERENV('INSTANCEID') too, just for some real OPS/RAC-ness! Best of > all, it fits the DBA-half of your brain by being fully cacheable and > non-pinging... > > ...of course, you can embed the use of the SEQUENCE object inside the > function; I left it on the "outside" in this example just to make it more > flexible with regard to which sequence object it uses... > > If they don't like the idea of using a stored function to get the sequence > number, then tell 'em that "it's more ANSI standard that way" and it's > "database independent". That gets 'em every time... > > Hope this helps... > > -Tim > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, September 03, 2002 5:54 PM > > > Unfortunately, we have an application dependency and I was required > > to come up with a quick & dirty fix. Thanks for your reply. > > > > On 2002.09.03 19:10 Anjo Kolk wrote: > > > If you run OPS and specify order, it works like no cache. > > > > > > My question to you: "Why cripple OPS and your business performance by > > > having this requirement ?" Spending a few bucks to get rid of this > > > dependency will improve the performance, until you run in to the next > > > problem ;-) > > > > > > Anjo. > > > > > > On Wednesday 04 September 2002 00:00, you wrote: > > > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > > > > 8.1.7.1) > > > > and I'm having an application dependency on a temporal order of > > > > sequence numbers. > > > > With OPS that becomes a problem because each node caches a set of > > > > sequence numbers > > > > (20 by default). Oracle has an option, specifically for that > > > > situation, namely "ORDER". > > > > My question is whether ORDER is the same thing as NOCACHE and whether > > > > it is possible > > > > to have a NOCACHE sequence which will return numbers in an incorrect > > > > order (larger number > > > > before the smaller one). > > > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a > > > > beer when I see you. > > > > Mladen Gogala > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: Anjo Kolk > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > > San Diego, California-- Public Internet access / Mailing Lists > > > > > > 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 sub
Re: OPS Sequences: nocache == order ??
1) I think that the tpc numbers are done represented in Transactons Per Minute (TPM/C) and not Per second. So event with 50 tpm/c it means around 8000 tps. 2) Inserting 13000 rows with direct I/O doesn't mean you did 13000 transactions. It could be one transaction 3) I have seen the theoretical limit, but if I recall correctly it was the number of SCN numbers that was generated. That way they can calculate how long it takes before the SCN number will wrap (it is only 48 bits). That is way in the future. Anjo. On Saturday 07 September 2002 04:08, you wrote: > One of our accelerator control system developers, an Oracle neophyte, > claims to have achieved 13,000 tps writing to a RAID 5 array. I did set up > the database, but most of the credit goes to him for exploring the OCI > direct I/O options. I have no verified the rate, but I have no reason > whatsoever to doubt him. > > This is on older four processor sun box. We've now traded in the lone > a-1000 ,attached two T3's, and turned on archive logging. I had him retest > and he said it was quicker than before . It's still RAID 5. If you are > wondering why RAID 5, we have another little 659.9 Terabyte database and > thousands of machines in compute farms to process the associated data. > That project has first choice, and the rest of us make do with what's left. > > I too am curious where this theoretical limit of 16384 comes from. > Theoretical as it no matter what hardware one chose this limit could not be > surpassed? > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] > > > > > > -Original Message- > Sent: Friday, September 06, 2002 4:38 PM > To: Multiple recipients of list ORACLE-L > > On Wednesday 04 September 2002 09:53, Tim Gorman wrote: > > Thinking more about it last night... > > > > Since Oracle's theoretical limit is 16384 commits per second, I imagine > > that you could safely make the sequence recycle at (or 16384 or > > 9) and limit the number of digits contributed by the sequence to > > 4-5... > > Really? What have they done in the past to get those astronomical TPS > numbers on some of their bencmarks? > > I'm pretty sure they were in excess of that number. > > IIRC, they were done on an nCube using OPS and about 400 CPUs. > > Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPS Sequences: nocache == order ??
One of our accelerator control system developers, an Oracle neophyte, claims to have achieved 13,000 tps writing to a RAID 5 array. I did set up the database, but most of the credit goes to him for exploring the OCI direct I/O options. I have no verified the rate, but I have no reason whatsoever to doubt him. This is on older four processor sun box. We've now traded in the lone a-1000 ,attached two T3's, and turned on archive logging. I had him retest and he said it was quicker than before . It's still RAID 5. If you are wondering why RAID 5, we have another little 659.9 Terabyte database and thousands of machines in compute farms to process the associated data. That project has first choice, and the rest of us make do with what's left. I too am curious where this theoretical limit of 16384 comes from. Theoretical as it no matter what hardware one chose this limit could not be surpassed? Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, September 06, 2002 4:38 PM To: Multiple recipients of list ORACLE-L On Wednesday 04 September 2002 09:53, Tim Gorman wrote: > Thinking more about it last night... > > Since Oracle's theoretical limit is 16384 commits per second, I imagine > that you could safely make the sequence recycle at (or 16384 or 9) > and limit the number of digits contributed by the sequence to 4-5... > Really? What have they done in the past to get those astronomical TPS numbers on some of their bencmarks? I'm pretty sure they were in excess of that number. IIRC, they were done on an nCube using OPS and about 400 CPUs. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
On Wednesday 04 September 2002 09:53, Tim Gorman wrote: > Thinking more about it last night... > > Since Oracle's theoretical limit is 16384 commits per second, I imagine > that you could safely make the sequence recycle at (or 16384 or 9) > and limit the number of digits contributed by the sequence to 4-5... > Really? What have they done in the past to get those astronomical TPS numbers on some of their bencmarks? I'm pretty sure they were in excess of that number. IIRC, they were done on an nCube using OPS and about 400 CPUs. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
Thinking more about it last night... Since Oracle's theoretical limit is 16384 commits per second, I imagine that you could safely make the sequence recycle at (or 16384 or 9) and limit the number of digits contributed by the sequence to 4-5... Also, you can get rid of the "wasteful" query on DUAL by including either X$DUAL (referencing previous ORACLE-L threads on DUAL vs X$DUAL plus good related stuff on http://www.optimaldba.com) or just use centi-second info from V$TIMER instead of X$DUAL. Either way makes for zero logical reads and (most importantly) zero physical reads thus zero pings... - Original Message - From: Gogala, Mladen To: Multiple recipients of list ORACLE-L Sent: Wednesday, September 04, 2002 9:03 AM Subject: RE: OPS Sequences: nocache == order ?? Neat idea. Thanks! -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 2:28 AMTo: Multiple recipients of list ORACLE-LSubject: Re: OPS Sequences: nocache == order ?? Mladen, Is there any way to have developers/users access the sequence via a function, instead of accessing the sequence directly? If so, then perhaps you could modify the sequence to add the temporal component, while maintaining the use of a cached sequence for uniqueness? Such as: SQL> create or replace function gen_seqq(in_seq in number) 2 return number 3 as 4 v_return_nbr number; 5 begin 6 select to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,''))) 7 into v_return_nbr 8 from dual; 9 return v_return_nbr; 10* end gen_seqq;SQL> / Function created. SQL> create table x (y number); Table created. SQL> create sequence xq; Sequence created. SQL> insert into x values (gen_seqq(xq.nextval)); 1 row created. SQL> Big and ugly numbers yes, but I think some folks get a strange thrill out of 20-digit numbers. It fits the requirement of being temporal (to the second, at least) and unique. You can throw in HSECS from V$TIMER if someone gets picky enough to want to go to the centi-second level as well. Yeah, and you can throw in USERENV('INSTANCEID') too, just for some real OPS/RAC-ness! Best of all, it fits the DBA-half of your brain by being fully cacheable and non-pinging... ...of course, you can embed the use of the SEQUENCE object inside the function; I left it on the "outside" in this example just to make it more flexible with regard to which sequence object it uses... If they don't like the idea of using a stored function to get the sequence number, then tell 'em that "it's more ANSI standard that way" and it's "database independent". That gets 'em every time... Hope this helps... -Tim - Original Message - From: "Mladen Gogala" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 03, 2002 5:54 PM Subject: Re: OPS Sequences: nocache == order ?? > Unfortunately, we have an application dependency and I was required > to come up with a quick & dirty fix. Thanks for your reply.> > > On 2002.09.03 19:10 Anjo Kolk wrote:> > > > If you run OPS and specify order, it works like no cache. > > > > My question to you: "Why cripple OPS and your business performance by having > > this requirement ?" Spending a few bucks to get rid of this dependency will > > improve the performance, until you run in to the next problem ;-)> > > > Anjo.> > > > > > > > > > > On Wednesday 04 September 2002 00:00, you wrote:> > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS> > > 8.1.7.1)> > > and I'm having an application dependency on a temporal order of sequence> > > numbers.> > > With OPS that becomes a problem because each node caches a set of sequence> > > numbers> > > (20 by default). Oracle has an option, specifically for that situation,> > > namely "ORDER".> > > My question is whether ORDER is the same thing as NOCACHE and whether it is> > > possible> > > to have a NOCACHE sequence which will return numbers in an incorrect ord
RE: OPS Sequences: nocache == order ??
Neat idea. Thanks! -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]]Sent: Wednesday, September 04, 2002 2:28 AMTo: Multiple recipients of list ORACLE-LSubject: Re: OPS Sequences: nocache == order ?? Mladen, Is there any way to have developers/users access the sequence via a function, instead of accessing the sequence directly? If so, then perhaps you could modify the sequence to add the temporal component, while maintaining the use of a cached sequence for uniqueness? Such as: SQL> create or replace function gen_seqq(in_seq in number) 2 return number 3 as 4 v_return_nbr number; 5 begin 6 select to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,''))) 7 into v_return_nbr 8 from dual; 9 return v_return_nbr; 10* end gen_seqq;SQL> / Function created. SQL> create table x (y number); Table created. SQL> create sequence xq; Sequence created. SQL> insert into x values (gen_seqq(xq.nextval)); 1 row created. SQL> Big and ugly numbers yes, but I think some folks get a strange thrill out of 20-digit numbers. It fits the requirement of being temporal (to the second, at least) and unique. You can throw in HSECS from V$TIMER if someone gets picky enough to want to go to the centi-second level as well. Yeah, and you can throw in USERENV('INSTANCEID') too, just for some real OPS/RAC-ness! Best of all, it fits the DBA-half of your brain by being fully cacheable and non-pinging... ...of course, you can embed the use of the SEQUENCE object inside the function; I left it on the "outside" in this example just to make it more flexible with regard to which sequence object it uses... If they don't like the idea of using a stored function to get the sequence number, then tell 'em that "it's more ANSI standard that way" and it's "database independent". That gets 'em every time... Hope this helps... -Tim - Original Message - From: "Mladen Gogala" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 03, 2002 5:54 PM Subject: Re: OPS Sequences: nocache == order ?? > Unfortunately, we have an application dependency and I was required > to come up with a quick & dirty fix. Thanks for your reply.> > > On 2002.09.03 19:10 Anjo Kolk wrote:> > > > If you run OPS and specify order, it works like no cache. > > > > My question to you: "Why cripple OPS and your business performance by having > > this requirement ?" Spending a few bucks to get rid of this dependency will > > improve the performance, until you run in to the next problem ;-)> > > > Anjo.> > > > > > > > > > > On Wednesday 04 September 2002 00:00, you wrote:> > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS> > > 8.1.7.1)> > > and I'm having an application dependency on a temporal order of sequence> > > numbers.> > > With OPS that becomes a problem because each node caches a set of sequence> > > numbers> > > (20 by default). Oracle has an option, specifically for that situation,> > > namely "ORDER".> > > My question is whether ORDER is the same thing as NOCACHE and whether it is> > > possible> > > to have a NOCACHE sequence which will return numbers in an incorrect order> > > (larger number> > > before the smaller one).> > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer> > > when I see you.> > > Mladen Gogala> > > > > > --> > Please see the official ORACLE-L FAQ: http://www.orafaq.com> > --> > Author: Anjo Kolk> > INET: [EMAIL PROTECTED]> > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051> > San Diego, California -- Public Internet access / Mailing Lists> > > > 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
Re: OPS Sequences: nocache == order ??
A day late and a dollar short but here's my $.02 Order will give you the temporal sequencing. Nocache should but it's not certain. Cached numbers are stored in the SYSTEM tablespace and can be retrieved in an atemporal order. I can't give you any specifics, but that's what Oracle says. Nocached numbers are generated at call time but that doesn't mean that they'll be stored in the database in temporal order. Order means that the number will be generated and stored in temporal order. As you can guess, this slows things up a bit. You will almost certainly see an increase in locking with ordered sequences. It can also happen with nocache. We recently went through an exercise of looking at every sequence in our database, about 400 altogether, to see if they needed to be ordered and cached. None of them needed to be ordered. Your requirement is unusual. If the sequence was being hit once an hour or so we decided to nocache it (save churning the SYSTEM tablespace). But our defaults are cache and noorder. HTH "Gogala, Mladen" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: OPS Sequences: nocache == order ?? Sent by: root 09/03/2002 06:00 PM Please respond to ORACLE-L I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
Mladen, Is there any way to have developers/users access the sequence via a function, instead of accessing the sequence directly? If so, then perhaps you could modify the sequence to add the temporal component, while maintaining the use of a cached sequence for uniqueness? Such as: SQL> create or replace function gen_seqq(in_seq in number) 2 return number 3 as 4 v_return_nbr number; 5 begin 6 select to_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,''))) 7 into v_return_nbr 8 from dual; 9 return v_return_nbr; 10* end gen_seqq;SQL> / Function created. SQL> create table x (y number); Table created. SQL> create sequence xq; Sequence created. SQL> insert into x values (gen_seqq(xq.nextval)); 1 row created. SQL> Big and ugly numbers yes, but I think some folks get a strange thrill out of 20-digit numbers. It fits the requirement of being temporal (to the second, at least) and unique. You can throw in HSECS from V$TIMER if someone gets picky enough to want to go to the centi-second level as well. Yeah, and you can throw in USERENV('INSTANCEID') too, just for some real OPS/RAC-ness! Best of all, it fits the DBA-half of your brain by being fully cacheable and non-pinging... ...of course, you can embed the use of the SEQUENCE object inside the function; I left it on the "outside" in this example just to make it more flexible with regard to which sequence object it uses... If they don't like the idea of using a stored function to get the sequence number, then tell 'em that "it's more ANSI standard that way" and it's "database independent". That gets 'em every time... Hope this helps... -Tim - Original Message - From: "Mladen Gogala" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 03, 2002 5:54 PM Subject: Re: OPS Sequences: nocache == order ?? > Unfortunately, we have an application dependency and I was required > to come up with a quick & dirty fix. Thanks for your reply.> > > On 2002.09.03 19:10 Anjo Kolk wrote:> > > > If you run OPS and specify order, it works like no cache. > > > > My question to you: "Why cripple OPS and your business performance by having > > this requirement ?" Spending a few bucks to get rid of this dependency will > > improve the performance, until you run in to the next problem ;-)> > > > Anjo.> > > > > > > > > > > On Wednesday 04 September 2002 00:00, you wrote:> > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS> > > 8.1.7.1)> > > and I'm having an application dependency on a temporal order of sequence> > > numbers.> > > With OPS that becomes a problem because each node caches a set of sequence> > > numbers> > > (20 by default). Oracle has an option, specifically for that situation,> > > namely "ORDER".> > > My question is whether ORDER is the same thing as NOCACHE and whether it is> > > possible> > > to have a NOCACHE sequence which will return numbers in an incorrect order> > > (larger number> > > before the smaller one).> > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer> > > when I see you.> > > Mladen Gogala> > > > > > --> > Please see the official ORACLE-L FAQ: http://www.orafaq.com> > --> > Author: Anjo Kolk> > INET: [EMAIL PROTECTED]> > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051> > San Diego, California -- Public Internet access / Mailing Lists> > > > 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> -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com> -- > Author: Mladen Gogala> INET: [EMAIL PROTECTED]> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051> San Diego, California -- Public Internet access / Mailing Lists> > 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).
Re: OPS Sequences: nocache == order ??
I agree with Anoj, you need to talk to the business folks to remove this dependency. Else you may encounter waits/queues on getting the next sequence numbers. One of the benfits in OPS and in RAC is the sequence cache option, because each instance will not have to query the Oracle's fast cache areas for the next sequence or wait in queue to get the next number. Any ways if you see slowness you now where to look! Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 03 Sep 2002 15:54:06 -0800 Unfortunately, we have an application dependency and I was required to come up with a quick & dirty fix. Thanks for your reply. On 2002.09.03 19:10 Anjo Kolk wrote: > > If you run OPS and specify order, it works like no cache. > > My question to you: "Why cripple OPS and your business performance by having > this requirement ?" Spending a few bucks to get rid of this dependency will > improve the performance, until you run in to the next problem ;-) > > Anjo. > > > > On Wednesday 04 September 2002 00:00, you wrote: > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > > 8.1.7.1) > > and I'm having an application dependency on a temporal order of sequence > > numbers. > > With OPS that becomes a problem because each node caches a set of sequence > > numbers > > (20 by default). Oracle has an option, specifically for that situation, > > namely "ORDER". > > My question is whether ORDER is the same thing as NOCACHE and whether it is > > possible > > to have a NOCACHE sequence which will return numbers in an incorrect order > > (larger number > > before the smaller one). > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer > > when I see you. > > Mladen Gogala > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Anjo Kolk > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). Murali Vallath Oracle Certified DBA http://www8.ewebcity.com/muralivallath/ http://www.summerksyus.com/ _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murali Vallath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
Unfortunately, we have an application dependency and I was required to come up with a quick & dirty fix. Thanks for your reply. On 2002.09.03 19:10 Anjo Kolk wrote: > > If you run OPS and specify order, it works like no cache. > > My question to you: "Why cripple OPS and your business performance by having > this requirement ?" Spending a few bucks to get rid of this dependency will > improve the performance, until you run in to the next problem ;-) > > Anjo. > > > > On Wednesday 04 September 2002 00:00, you wrote: > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > > 8.1.7.1) > > and I'm having an application dependency on a temporal order of sequence > > numbers. > > With OPS that becomes a problem because each node caches a set of sequence > > numbers > > (20 by default). Oracle has an option, specifically for that situation, > > namely "ORDER". > > My question is whether ORDER is the same thing as NOCACHE and whether it is > > possible > > to have a NOCACHE sequence which will return numbers in an incorrect order > > (larger number > > before the smaller one). > > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer > > when I see you. > > Mladen Gogala > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Anjo Kolk > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPS Sequences: nocache == order ??
The way I see it is: If you specify ORDER then the only way Oracle can enforce this is getting it from the dictionary which means no caching will be implemented. If you need the data to be ordered then (in my opinion) it's better to declare what you need by using option "ORDER". Using option "NOCACHE" alone believing it will give you the same functionality will not be guaranteed from one release to the other. Waleed -Original Message- Sent: Tuesday, September 03, 2002 7:29 PM To: Multiple recipients of list ORACLE-L Yes, but when analyzed, it turns out that NOCACHE will also yield ordered results. What I'm interested in are internal differences in behavior. My assumption is that with ORDER oracle queries the instances directly, while NOCACHE will simply read/write everything from the disk. On 2002.09.03 18:38 "Khedr, Waleed" wrote: > It looks like when option "ORDER" is used Oracle guarantees the generated > values will be in order since the "CACHE" option will be ignored by Oracle > even if it was requested. > > This is in the parallel mode. > > Look at note: Note:1031850.6 > > Waleed > > -Original Message- > Sent: Tuesday, September 03, 2002 6:00 PM > To: Multiple recipients of list ORACLE-L > > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > 8.1.7.1) > and I'm having an application dependency on a temporal order of sequence > numbers. > With OPS that becomes a problem because each node caches a set of sequence > numbers > (20 by default). Oracle has an option, specifically for that situation, > namely "ORDER". > My question is whether ORDER is the same thing as NOCACHE and whether it is > possible > to have a NOCACHE sequence which will return numbers in an incorrect order > (larger number > before the smaller one). > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer > when I see you. > Mladen Gogala > > -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
Yes, but when analyzed, it turns out that NOCACHE will also yield ordered results. What I'm interested in are internal differences in behavior. My assumption is that with ORDER oracle queries the instances directly, while NOCACHE will simply read/write everything from the disk. On 2002.09.03 18:38 "Khedr, Waleed" wrote: > It looks like when option "ORDER" is used Oracle guarantees the generated > values will be in order since the "CACHE" option will be ignored by Oracle > even if it was requested. > > This is in the parallel mode. > > Look at note: Note:1031850.6 > > Waleed > > -Original Message- > Sent: Tuesday, September 03, 2002 6:00 PM > To: Multiple recipients of list ORACLE-L > > > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > 8.1.7.1) > and I'm having an application dependency on a temporal order of sequence > numbers. > With OPS that becomes a problem because each node caches a set of sequence > numbers > (20 by default). Oracle has an option, specifically for that situation, > namely "ORDER". > My question is whether ORDER is the same thing as NOCACHE and whether it is > possible > to have a NOCACHE sequence which will return numbers in an incorrect order > (larger number > before the smaller one). > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer > when I see you. > Mladen Gogala > > -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: OPS Sequences: nocache == order ??
If you run OPS and specify order, it works like no cache. My question to you: "Why cripple OPS and your business performance by having this requirement ?" Spending a few bucks to get rid of this dependency will improve the performance, until you run in to the next problem ;-) Anjo. On Wednesday 04 September 2002 00:00, you wrote: > I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS > 8.1.7.1) > and I'm having an application dependency on a temporal order of sequence > numbers. > With OPS that becomes a problem because each node caches a set of sequence > numbers > (20 by default). Oracle has an option, specifically for that situation, > namely "ORDER". > My question is whether ORDER is the same thing as NOCACHE and whether it is > possible > to have a NOCACHE sequence which will return numbers in an incorrect order > (larger number > before the smaller one). > Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer > when I see you. > Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: OPS Sequences: nocache == order ??
It looks like when option "ORDER" is used Oracle guarantees the generated values will be in order since the "CACHE" option will be ignored by Oracle even if it was requested. This is in the parallel mode. Look at note: Note:1031850.6 Waleed -Original Message-From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]Sent: Tuesday, September 03, 2002 6:00 PMTo: Multiple recipients of list ORACLE-LSubject: OPS Sequences: nocache == order ?? I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala
OPS Sequences: nocache == order ??
I'm managing an OPS configuration (4x HP 9000/N, HP-UX 11/64 , RDBMS 8.1.7.1) and I'm having an application dependency on a temporal order of sequence numbers. With OPS that becomes a problem because each node caches a set of sequence numbers (20 by default). Oracle has an option, specifically for that situation, namely "ORDER". My question is whether ORDER is the same thing as NOCACHE and whether it is possible to have a NOCACHE sequence which will return numbers in an incorrect order (larger number before the smaller one). Please, o OPS gods and godesses, help me out and I'll sacrifice you a beer when I see you. Mladen Gogala
RE: Is there a way to create NOLOG Sequences?
Hannah, Increase the cache value. The only time a sequence change get's recorded in the redo logs is when it goes to grab another set of values. If you look at the sys.seq$ table, that's only updated when you run out of cache values, and oracle needs to grab another one. Nick -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 13, 2002 8:30 AMTo: Multiple recipients of list ORACLE-LSubject: Is there a way to create NOLOG Sequences? Hi, Having an issue where some code is constantly hitting a sequence (yes, I know... this will be fixed also). But in the meanwhile, is there a way to NOT log sequences to the redo log Thanks, Hannah
Is there a way to create NOLOG Sequences?
Hi, Having an issue where some code is constantly hitting a sequence (yes, I know... this will be fixed also). But in the meanwhile, is there a way to NOT log sequences to the redo log Thanks, Hannah
RE: Creating sequences on the DUAL table?
An interesting use of sequences might be to encrypt information. Properly sequenced sequences could be used to easily encode information that would be destroyed upon being read. Know, I dunno where that came from, just hit me out of the blue while reading this thread. Could be fun though. Jared "Fink, Dan" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/30/2002 01:31 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:RE: Creating sequences on the DUAL table? To be more accurate, a sequence does not even have to be used for a table. It is a construct to populate a variable (could be a table column, could be run time) with a number that is generated in a specific order (though it may contain gaps). Dan (as he ducks a knitting needle). -Original Message- Sent: Tuesday, July 30, 2002 2:03 PM To: Multiple recipients of list ORACLE-L Cherie, Terminology -- make sure they understand that sequences are not created attached to any table and can be used for more than one table. Rachel --- [EMAIL PROTECTED] wrote: > > Dan, > > Haven't heard the why yet but I think it may be just a poorly worded > request. I suspect he wants the sequences created on another table > and > just wants to be able to select from dual. Maybe he never realized > that > the sequences were actually created on another table.Maybe he > actually > thought that the sequences were created on the DUAL table since he > only > ever used them by selecting from DUAL. Our developers run quite a > wide > gamut and I don't work very regularly with this one so it's hard to > know > where he's coming from. Still trying to get him on the phone. > > Thanks for this info about performance on DUAL. I will pass the info > on. > Thanks to everyone for getting us all on the same page. I'm sure > that > once I talk this out with him, it'll be straightened out quickly. > > Cherie > > > > > "Fink, Dan" > > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, > com> > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > cc: > > 07/30/02 12:46 Subject: RE: Creating > sequences on the DUAL table? > PM > > > > > > > > > > Cherie, >My first response is 'WHY?'. Do they mean that they want > to > create 2 > new sequences and use the DUAL table to retrieve the values? It will > cause > performance problems and there are better solutions. I have some > information > on the performance implications of DUAL at > http://www.optimaldba.com/internals/oraint_dual.html. > > Dan Fink > > -Original Message- > Sent: Tuesday, July 30, 2002 11:54 AM > To: Multiple recipients of list ORACLE-L > > > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a request in > the > past. I asked for clarification on why this is needed and I didn't > get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create sequences on > the > DUAL table? Should I just flat-out refuse this request and if so, > why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (8
RE: Creating sequences on the DUAL table?
To be more accurate, a sequence does not even have to be used for a table. It is a construct to populate a variable (could be a table column, could be run time) with a number that is generated in a specific order (though it may contain gaps). Dan (as he ducks a knitting needle). -Original Message- Sent: Tuesday, July 30, 2002 2:03 PM To: Multiple recipients of list ORACLE-L Cherie, Terminology -- make sure they understand that sequences are not created attached to any table and can be used for more than one table. Rachel --- [EMAIL PROTECTED] wrote: > > Dan, > > Haven't heard the why yet but I think it may be just a poorly worded > request. I suspect he wants the sequences created on another table > and > just wants to be able to select from dual. Maybe he never realized > that > the sequences were actually created on another table.Maybe he > actually > thought that the sequences were created on the DUAL table since he > only > ever used them by selecting from DUAL. Our developers run quite a > wide > gamut and I don't work very regularly with this one so it's hard to > know > where he's coming from. Still trying to get him on the phone. > > Thanks for this info about performance on DUAL. I will pass the info > on. > Thanks to everyone for getting us all on the same page. I'm sure > that > once I talk this out with him, it'll be straightened out quickly. > > Cherie > > > > > "Fink, Dan" > > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, > com> > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > cc: > > 07/30/02 12:46 Subject: RE: Creating > sequences on the DUAL table? > PM > > > > > > > > > > Cherie, >My first response is 'WHY?'. Do they mean that they want > to > create 2 > new sequences and use the DUAL table to retrieve the values? It will > cause > performance problems and there are better solutions. I have some > information > on the performance implications of DUAL at > http://www.optimaldba.com/internals/oraint_dual.html. > > Dan Fink > > -Original Message- > Sent: Tuesday, July 30, 2002 11:54 AM > To: Multiple recipients of list ORACLE-L > > > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a request in > the > past. I asked for clarification on why this is needed and I didn't > get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create sequences on > the > DUAL table? Should I just flat-out refuse this request and if so, > why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Ser
RE: Creating sequences on the DUAL table?
Cherie, Terminology -- make sure they understand that sequences are not created attached to any table and can be used for more than one table. Rachel --- [EMAIL PROTECTED] wrote: > > Dan, > > Haven't heard the why yet but I think it may be just a poorly worded > request. I suspect he wants the sequences created on another table > and > just wants to be able to select from dual. Maybe he never realized > that > the sequences were actually created on another table.Maybe he > actually > thought that the sequences were created on the DUAL table since he > only > ever used them by selecting from DUAL. Our developers run quite a > wide > gamut and I don't work very regularly with this one so it's hard to > know > where he's coming from. Still trying to get him on the phone. > > Thanks for this info about performance on DUAL. I will pass the info > on. > Thanks to everyone for getting us all on the same page. I'm sure > that > once I talk this out with him, it'll be straightened out quickly. > > Cherie > > > > > "Fink, Dan" > > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, > com> > "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > cc: > > 07/30/02 12:46 Subject: RE: Creating > sequences on the DUAL table? > PM > > > > > > > > > > Cherie, >My first response is 'WHY?'. Do they mean that they want > to > create 2 > new sequences and use the DUAL table to retrieve the values? It will > cause > performance problems and there are better solutions. I have some > information > on the performance implications of DUAL at > http://www.optimaldba.com/internals/oraint_dual.html. > > Dan Fink > > -Original Message- > Sent: Tuesday, July 30, 2002 11:54 AM > To: Multiple recipients of list ORACLE-L > > > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a request in > the > past. I asked for clarification on why this is needed and I didn't > get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create sequences on > the > DUAL table? Should I just flat-out refuse this request and if so, > why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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, inclu
RE: Creating sequences on the DUAL table?
Mladen, Thanks for everyone's response on this. I think that this is just a misperception on the part of the developer. I took the phrase verbatim from his change request. I suspect that he has only ever used sequences in the SELECT from DUAL statement so he thinks that they are actual objects associated with dual. I'll clear it up once I get him on the phone. Just wanted to make sure that this wasn't some weird new thing that I'd just never heard off. Cherie "Gogala, Mladen" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Creating sequences on the DUAL table? Sent by: [EMAIL PROTECTED] om 07/30/02 02:15 PM Please respond to ORACLE-L What are "sequence on the table"? All I know about sequences is that they are entities for fas generation of unique numbers without encountering locks. They are standalone entries without much connection to any other object. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 30, 2002 1:54 PM > To: Multiple recipients of list ORACLE-L > Subject: Creating sequences on the DUAL table? > > > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a > request in the > past. I asked for clarification on why this is needed and I > didn't get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create > sequences on the > DUAL table? Should I just flat-out refuse this request and > if so, why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: Creating sequences on the DUAL table?
I would think they probably saw something like select sequence_name.nextval from dual; Maybe they thought the sequence and dual were connected in some manner. -Original Message- Sent: Tuesday, July 30, 2002 2:15 PM To: Multiple recipients of list ORACLE-L Cherie, You don't create sequences on a table, they are objects in and of themselves. So you can create the sequences for the developers but I'm wondering where they got the notion that sequences were created on a table. And why they want to use DUAL. Rachel --- [EMAIL PROTECTED] wrote: > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a request in > the > past. I asked for clarification on why this is needed and I didn't > get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create sequences on > the > DUAL table? Should I just flat-out refuse this request and if so, > why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Cherie, If you push F1 key from SqlPlus there will be a complete explanation called 'all about sequences and how to create them on dual table'. -Original Message- Sent: Tuesday, July 30, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Dan, Haven't heard the why yet but I think it may be just a poorly worded request. I suspect he wants the sequences created on another table and just wants to be able to select from dual. Maybe he never realized that the sequences were actually created on another table.Maybe he actually thought that the sequences were created on the DUAL table since he only ever used them by selecting from DUAL. Our developers run quite a wide gamut and I don't work very regularly with this one so it's hard to know where he's coming from. Still trying to get him on the phone. Thanks for this info about performance on DUAL. I will pass the info on. Thanks to everyone for getting us all on the same page. I'm sure that once I talk this out with him, it'll be straightened out quickly. Cherie "Fink, Dan" , com> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: 07/30/02 12:46 Subject: RE: Creating sequences on the DUAL table? PM Cherie, My first response is 'WHY?'. Do they mean that they want to create 2 new sequences and use the DUAL table to retrieve the values? It will cause performance problems and there are better solutions. I have some information on the performance implications of DUAL at http://www.optimaldba.com/internals/oraint_dual.html. Dan Fink -Original Message- Sent: Tuesday, July 30, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Lyuda Hoska INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Kevin, Thanks for the suggestion on select statements. I will pass them on. I think it's just a misunderstanding on his part as to where the sequences are actually created. Apparently he only uses them in a select from dual so he thinks they reside there. Cherie "kkennedy" point.com>cc: Sent by: Subject: RE: Creating sequences on the DUAL table? [EMAIL PROTECTED] m 07/30/02 01:28 PM Please respond to ORACLE-L H? Sequences are not created "on" the dual table. Sequences exist as independent entities. Ofttimes, sequence values are selected using the dual table (e.g., select seqname.nextval from dual) -- this is a coding choice. Performance of queries that select from the dual table are generally not "inviting poor performance" (although I've seen some postings on how to improve their performance). If I were you, I would do some more reading up on sequences in the Concepts manual and not worry too much about the performance. Suggest to the developers that they use the sequences directly whenever possible rather than selecting from dual as in the following: One method: select seqname.nextval into local_var from dual; insert into destination_table (...id_column...) values (...local_var...); Better method: insert into destination_table (...id_column...) values (...seqname.nextval...); And, if the value is needed for other things: insert into destination_table (...id_column...) values (...seqname.nextval...) returning id_column into local_var; HTH Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Tuesday, July 30, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PR
RE: Creating sequences on the DUAL table?
Dan, Haven't heard the why yet but I think it may be just a poorly worded request. I suspect he wants the sequences created on another table and just wants to be able to select from dual. Maybe he never realized that the sequences were actually created on another table.Maybe he actually thought that the sequences were created on the DUAL table since he only ever used them by selecting from DUAL. Our developers run quite a wide gamut and I don't work very regularly with this one so it's hard to know where he's coming from. Still trying to get him on the phone. Thanks for this info about performance on DUAL. I will pass the info on. Thanks to everyone for getting us all on the same page. I'm sure that once I talk this out with him, it'll be straightened out quickly. Cherie "Fink, Dan" , com> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: 07/30/02 12:46 Subject: RE: Creating sequences on the DUAL table? PM Cherie, My first response is 'WHY?'. Do they mean that they want to create 2 new sequences and use the DUAL table to retrieve the values? It will cause performance problems and there are better solutions. I have some information on the performance implications of DUAL at http://www.optimaldba.com/internals/oraint_dual.html. Dan Fink -Original Message- Sent: Tuesday, July 30, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
What are "sequence on the table"? All I know about sequences is that they are entities for fas generation of unique numbers without encountering locks. They are standalone entries without much connection to any other object. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 30, 2002 1:54 PM > To: Multiple recipients of list ORACLE-L > Subject: Creating sequences on the DUAL table? > > > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a > request in the > past. I asked for clarification on why this is needed and I > didn't get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create > sequences on the > DUAL table? Should I just flat-out refuse this request and > if so, why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: Creating sequences on the DUAL table?
Cherie, You don't create sequences on a table, they are objects in and of themselves. So you can create the sequences for the developers but I'm wondering where they got the notion that sequences were created on a table. And why they want to use DUAL. Rachel --- [EMAIL PROTECTED] wrote: > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a request in > the > past. I asked for clarification on why this is needed and I didn't > get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create sequences on > the > DUAL table? Should I just flat-out refuse this request and if so, > why? > > Thanks, > > Cherie Machler > Oracle DBA > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > > 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!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Title: RE: Creating sequences on the DUAL table? Sequences are DB objects independent of any tables including dual (not possible to create a sequence "on" a table. Sequences are sometimes used to populate the PK of a table, but the actual sequence and the table are not structurally related. Such as: SQL> create sequence myseq; Sequence created. Then, often developers will use the dual table when grabbing the next value from the sequence. SQL> select myseq.nextval from dual; better to do this...insert into table1(id,name) values(myseq.nextval,'FName'); Check out the info in the docs on Sequences, maybe the concepts part would be a good start. HTH -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 1:54 PM To: Multiple recipients of list ORACLE-L Subject: Creating sequences on the DUAL table? I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Sounds like your developer hangs out with some of our developers! ;) I am sorry to say but, I guess the syntax "...from dual" could be confusing to the developer. Your understanding is just fine. Don't do anything with dual.. May be briefly explaining to the developer how sequence number work could take care of this request. Good Luck.. - Kirti -Original Message- Sent: Tuesday, July 30, 2002 12:54 PM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Communication gap and ignorance. The developers proably want to create new sequences. And they are used to writing queries as "Select sequencename.nextval from dual;" So they call it sequences on the dual table. "kkennedy" point.com> cc: Sent by: Subject: RE: Creating sequences on the DUAL table? [EMAIL PROTECTED] m July 30, 2002 02:28 PM Please respond to ORACLE-L H? Sequences are not created "on" the dual table. Sequences exist as independent entities. Ofttimes, sequence values are selected using the dual table (e.g., select seqname.nextval from dual) -- this is a coding choice. Performance of queries that select from the dual table are generally not "inviting poor performance" (although I've seen some postings on how to improve their performance). If I were you, I would do some more reading up on sequences in the Concepts manual and not worry too much about the performance. Suggest to the developers that they use the sequences directly whenever possible rather than selecting from dual as in the following: One method: select seqname.nextval into local_var from dual; insert into destination_table (...id_column...) values (...local_var...); Better method: insert into destination_table (...id_column...) values (...seqname.nextval...); And, if the value is needed for other things: insert into destination_table (...id_column...) values (...seqname.nextval...) returning id_column into local_var; HTH Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Tuesday, July 30, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail mes
RE: Creating sequences on the DUAL table?
Cherie, My first response is 'WHY?'. Do they mean that they want to create 2 new sequences and use the DUAL table to retrieve the values? It will cause performance problems and there are better solutions. I have some information on the performance implications of DUAL at http://www.optimaldba.com/internals/oraint_dual.html. Dan Fink -Original Message- Sent: Tuesday, July 30, 2002 11:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Creating sequences on the DUAL table?
Title: RE: Creating sequences on the DUAL table? answer below > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > I have a request from one of our developers to create two new > sequences on > the DUAL table. > > This seems like a bad idea to me. I've never had such a > request in the > past. I asked for clarification on why this is needed and I > didn't get a > lot of details yet. > > Is this something that is standard operating procedure? My > understanding > that interfacing with the DUAL table is usually inviting poor > performance. > Plus, I don't like to mess around with system tables, in general. > > Under what circumstances would it be justified to create > sequences on the > DUAL table? Should I just flat-out refuse this request and > if so, why? You don't mean that the developer wants to add columns to dual? I think the developer means that you should create two sequences, and he will get the next value by saying select seq.nextval from dual ; (example: SQL> create sequence s ; Séquence créée. SQL> select s.nextval from dual ; NEXTVAL - 1 end of example) I don't see a problem with creating a sequence for someone, as long as you make them beg properly first to teach them respect for the importance of the DBA.
RE: Creating sequences on the DUAL table?
H? Sequences are not created "on" the dual table. Sequences exist as independent entities. Ofttimes, sequence values are selected using the dual table (e.g., select seqname.nextval from dual) -- this is a coding choice. Performance of queries that select from the dual table are generally not "inviting poor performance" (although I've seen some postings on how to improve their performance). If I were you, I would do some more reading up on sequences in the Concepts manual and not worry too much about the performance. Suggest to the developers that they use the sequences directly whenever possible rather than selecting from dual as in the following: One method: select seqname.nextval into local_var from dual; insert into destination_table (...id_column...) values (...local_var...); Better method: insert into destination_table (...id_column...) values (...seqname.nextval...); And, if the value is needed for other things: insert into destination_table (...id_column...) values (...seqname.nextval...) returning id_column into local_var; HTH Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Tuesday, July 30, 2002 10:54 AM To: Multiple recipients of list ORACLE-L I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Creating sequences on the DUAL table?
I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard operating procedure? My understanding that interfacing with the DUAL table is usually inviting poor performance. Plus, I don't like to mess around with system tables, in general. Under what circumstances would it be justified to create sequences on the DUAL table? Should I just flat-out refuse this request and if so, why? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SEQUENCES
There's a short article on that subject at www.cybcon.com/~jkstill Jared On Thursday 04 April 2002 12:58, Seema Singh wrote: > Hi > I do export and import of one schema from one server to another.But > sequences are not matched.How to syncronise all sequences?Can I drop all > sequences and create all sequences? o > Thx > -Seema > > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Re: SEQUENCES
Seema, this gonna be more then that . after recreating sequences you will have to adjust them so that you dont get duplicate values . BP - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 04, 2002 12:58 PM > Hi > I do export and import of one schema from one server to another.But > sequences are not matched.How to syncronise all sequences?Can I drop all > sequences and create all sequences? o > Thx > -Seema > > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Seema Singh > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Big Planet INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SEQUENCES
Hi I do export and import of one schema from one server to another.But sequences are not matched.How to syncronise all sequences?Can I drop all sequences and create all sequences? o Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Tracing sequences (was re: freelist tesing)
if you return the seq nextval by a function you can track it. So, let your developers avoid 'select seq.nextval into ... from dual;' syntax. Then use autonomous transactions to log the next value that is being returned to another table, which you can monitor. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1