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).
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: 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
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
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).
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 seq.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 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 seq.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).
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 seq.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 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 seq.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 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 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
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 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
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- 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
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
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[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[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).
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 PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject
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 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 PROTECTED] wrote: Jonathan
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: 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: 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: 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 sequencessimply 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
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
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: 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).
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
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
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
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 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
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: 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: 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
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
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 -- --- 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
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
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: 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).
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
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: winmail.dat
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
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
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
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
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
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
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_table_name, 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_table_name1_table_name2 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
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
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 __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com http://sbc.yahoo.com
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-- 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: 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).
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
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 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: Igor Neyman INET: [EMAIL
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
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 send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
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
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 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
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: 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).
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 ??
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 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)
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 ??
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 ??
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 ??
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] MGogala cc: @oxhp.com 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 ??
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 addthe 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)
2return number 3
as 4 v_return_nbr number;
5 begin 6
selectto_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
intov_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 ??
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 to4-5...
Also, you can get rid of the "wasteful" query on
DUAL by including either X$DUAL (referencing previous ORACLE-L threads
onDUAL vs X$DUALplus 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 addthe 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)
2return number 3
as 4 v_return_nbr
number; 5 begin 6
selectto_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
intov_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
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: 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
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 ??
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 ??
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 ??
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 ??
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 addthe 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)
2return number 3
as 4 v_return_nbr number;
5 begin 6
selectto_number(to_char(sysdate,'MMDDHH24MISS')||ltrim(to_char(in_seq,'')))
7
intov_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).
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: 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
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?
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).
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?
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?
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 kkennedy@firstTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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
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?
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?
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?
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 Dan.Fink@mdx. To: '[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, 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 kkennedy@first To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] point.comcc: 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
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 Dan.Fink@mdx. To: '[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, 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?
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?
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] MGogala@oxhp. cc: com 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 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
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 Dan.Fink@mdx. To: '[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, 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
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 Dan.Fink@mdx. To: '[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, 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
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 Dan.Fink@mdx. To: '[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, 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
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).
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: 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).
Tracing sequences (was re: freelist tesing)
Anjo has convinced me that I was indeed barking up the wrong tree (and even if I was the tree didn't care) He lead me into tests that shows my problem exhibits exactly the same characteristics of trying to insert the same value into a unique index simultaneously. That somehow, one user does an insert with one value, and before he commits, another session (usually different user -- not always) tries to insert the same value. Previously I had examined the developers code and convinced myself that this could not be the case as he selects a sequence nextval into a variable, then immediately uses that variable to create a the value list for the insert. I haven't found anything that makes me want to mistrust a sequence nextval. (If anyone knows of one in 8.0.5 on Solars 2.7 please let me know.) So I've got to mistrust something going on in the developers VB based COM object running under MTX serving up ASP pages for IIS. (Notice the long string of MS products there and you can guess how that influences my suspicions.) Since I've come to this realization, the event has not recurred, so I don't have any statistics. But we do know that when it starts, we see incidents from all 8 webservers simultaneously. Past evidence collected for a blocker and a blocked session shows that they were on the same webserver, but that's just 1 data point and we don't have any other to confirm or deny that relationship. Also when it starts, it happens at a furious rate, dozens of sessions at once. Then it suddenly stops. Curiously, the same applications on the same webservers are handling 30 other databases which experience no problems. This points me back to the database. sigh One of the things I would like to do, is to record what the database thought it answered for the select of the sequence nextval, and have that for comparison when the application tries to do its insert. My dream would be to have a trace/log/journal/something that recorded the nextval returned,user,session,serial#,and sysdate for every time the sequence was read. This would allow me to see discrepancies in the select/insert and sessions that were trying to insert without actually making the select. Has anyone tried this level of tracing/logging before? -rje -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge 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
