Re: Reset sequence at midnight

> Did you request the lock in share mode to > request next val ?  This would help to reduce > the contention.  Normal users would then only > queue on the exclusive lock that you would > take for the fix-up. >    lock(shared) >    increment sequence >    release

Re: Reset sequence at midnight

me level of serialization. > > control access to the sequence through a package > > Within the package use a function that sets a lock via dbms_lock.request > and then immediately release the lock. > Did you request the lock in share mode to request next val ? This would help to re

Re: Reset sequence at midnight

Interesting, I've actually had to do this before. Be forewarned that this is not a good method to use for a very busy app, as it does introduce some level of serialization. control access to the sequence through a package Within the package use a function that sets a lock via dbms_lock.re

Re: Reset sequence at midnight

drop sequence create sequence but why do you want to do that? --- Oracle <[EMAIL PROTECTED]> wrote: > Hi, > > I have a sequence which i want to reset to 0 at midnight everyday. > > What is the best way to do this? > > Db version - 9.2.0.1.0 > Thanks > > I

Re: Reset sequence at midnight

To use program which will supply numbers, possibly as an external routine, and not use an oracle sequence as it wasn't designed for that purpose. On 01/14/2004 12:04:26 PM, Oracle wrote: > Hi, > > I have a sequence which i want to reset to 0 at midnight everyday. > > What i

Reset sequence at midnight

Hi, I have a sequence which i want to reset to 0 at midnight everyday. What is the best way to do this? Db version - 9.2.0.1.0 Thanks Imran -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051

Re: sequence

Joe you are absolutely right >From the sql reference : "Where to use currval and nextval If any of these locations contains references to both CURRVAL and NEXTVAL, then Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL." Sorry for the

Re: sequence

(mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start with 1 maxvalue 1000 nocycle nocache ; and 2. test is Name Null?Type ID

sequence

Hello list and a happy new year to everyone, why does : insert into test values (mysequence.nextval, mysequence.currval ) ; have the same effect as insert into test values (mysequence.currval, mysequence.nextval ) ; where 1. mysequence is create sequence mysequence increment by 1 start

RE: migration sequence oci problem

eroen   -Oorspronkelijk bericht- Van: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 27 november 2003 19:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: migration sequence oci problem   We could narrow it down to the value of the sequence exceeding 16777216 (2 to the

Re: trace sequence values

Event 10217 is listed as used to debug sequence numbers, but setting the event doesn't seem to have much effect on a trace file. You could try monitoring v$_sequences. Jared On Thu, 2003-11-27 at 03:34, Jeroen van Sluisdam wrote: > Hi, > > I need urgently to trace an app which

RE: migration sequence oci problem

Jeron, I don't know if it would help any, but if it is a DB issue, event 10217 might be useful: $ oerr ORA 10217 10217, 0, "debug sequence numbers" // *Cause: // *Action: I have never used it myself before, but if you push support a bit maybe they might provide details on

RE: migration sequence oci problem

We could narrow it down to the value of the sequence exceeding 16777216 (2 to the power of 24) This looks familiar as described in bug 2573172 This bug describes to change the type in the odefin call to type 3 but this doesn't help. I know this is not really a dba issue now anymor

trace sequence values

Hi,   I need urgently to trace an app which uses sequences with OCI to determine what is going wrong. When I use an 10046 event I do not get values of sequence returned when issueing sequence.nextval. Any ideas about how to trace this are appreciated.   Regards,   Jeroen

migration sequence oci problem

  Hi,   We're testing an oracle 9.2.0.4 database with an oracle 7 client. This is a C++ client, using OCI to go to oracle. We see strange behaviour when using a sequence which worked nicely before. The sequence is not incremented when issueing "select res_id.nextval

Sequence and order by

Is this documented anywhere? SQL> drop sequence test_seq; Sequence dropped. SQL> SQL> create sequence test_seq; Sequence created. SQL> SQL> select test_seq.nextval 2 from dual; NEXTVAL -- 1 SQL> SQL> select test_seq.nextval 2 from dual

RE: dropped sequence - backup/recovery question

Title: RE: dropped sequence - backup/recovery question That would work if a) I knew that the sequence was being used to create values for a column in a table, and b) it was only used for that purpose. Which is obviously not always the case. Another list member suggested to me that I use

Re: dropped sequence - backup/recovery question

Jacques,     If you know the field that is being populated by the sequence, examine the last 25/50 entries, determine the pattern and 'high' value, then recreate the sequence accordingly.     If you don't know the field, you can take a guess that it is the primary key (or

dropped sequence - backup/recovery question

Title: dropped sequence - backup/recovery question The question from Janardhana Babu Donga yesterday about how to recreate a dropped package/procedure/function made me think about something else. Imagine the following scenario: There is some commercial off-the-shelf (COTS) software package

RE: Sequence as column default

;John', 'Doe', 'jdoe', > '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, > 30); > >Same thing goes with currval also. >-- > >Alan Davey >[EMAIL PROTECTED] >718-482-4200 x106 > > >On 3/10/2003 3:49 P

RE: Sequence as column default

;use a trigger. > >INSERT INTO employees > VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', > '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, > 30); > >Same thing goes with currval also. >-- > >A

RE: Sequence as column default

Title: RE: Sequence as column default > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > > > Alan - You are correct. However, apparently that isn't easy > to do with J2EE / EJB, hense the trigger. Don't ask me why. > When will they come out with a book titl

Re: Sequence as column default

ll, > 30); > >Same thing goes with currval also. >-- > >Alan Davey >[EMAIL PROTECTED] >718-482-4200 x106 > > >On 3/10/2003 3:49 PM, DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: >>Has anyone defined a sequence as the default value for a column? >The &g

Re: Sequence as column default

PROTECTED]> cc: Subject:Re: Sequence as column default You could also do it as part of your insert statement if you didn't want to use a trigger. INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1

RE: Sequence as column default

55-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30); Same thing goes with currval also. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/10/2003 3:49 PM, DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: >Has anyone defined a sequence as the default value fo

Re: Sequence as column default

0); Same thing goes with currval also. -- Alan Davey [EMAIL PROTECTED] 718-482-4200 x106 On 3/10/2003 3:49 PM, DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: >Has anyone defined a sequence as the default value for a column? The >manual >is a little ambiguous (in my mind anyway): >

Sequence as column default

Has anyone defined a sequence as the default value for a column? The manual is a little ambiguous (in my mind anyway): In the 8.1.7 manual: Restriction: A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that

RE: Listener/Database shutdown sequence

OK, after emphatically stating it doesn't matter, I thought of a situation in which the order of shutting down could make a difference. In this scenario, you first must be the kind of person who needlessly insists on shutdown immediate (instead of abort). Second, you must have a very heavy rate o

RE: Listener/Database shutdown sequence

/auto-shutdown procedures run by root. >From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: Listener/Database shutdown sequence >Date: Mon, 03 Mar 2003 09:04:11

Re: Listener/Database shutdown sequence

Jeremiah I didn't really think it mattered for the shutdown. Though I know in Oracle 7 it did matter for the startup (we use MTS). Another DBA seemed to imply that the sequence was important on shutdown of 8i. So if in doubt I RTFM, suffer the metalink search and post to the list. As

Fwd: Re: Listener/Database shutdown sequence

From: Jeremiah Wilton <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Listener/Database shutdown sequence Date: Mon, 03 Mar 2003 08:19:06 -0800 Why do you/they think it matters, regardless of the documen

RE: Listener/Database shutdown sequence

Tom, It is for the server auto-start/auto-shutdown procedures run by root. From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: Listener/Database shutdown sequence Date: Mon

RE: Listener/Database shutdown sequence

Sometimes it does matter what is stopped rather than the order, but we can't tell why the original poster was asking. It depends on your environment. For example, one site I worked at had a separate listener for every database in their environment. Their reasoning was they made life easier for t

RE: Listener/Database shutdown sequence

David, it just doesn't matter. why do you even feel the need to shut the listener down? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, March 03, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Should the databases be shutdown before the listener

RE: Listener/Database shutdown sequence

There might be some docs somewhere but the issues are not as much technical as procedural. If you shut the database down, leave the listener up, and bring the database backup there are no connection problems except when the db is down. This implies that the instance and the listener are independe

Re: Listener/Database shutdown sequence

Why do you/they think it matters, regardless of the documentation? You can shut them down in any order you want. Users just get a different error if the listener is up vs. when it is down. Starting up, if services are registered via local_listener or mts_dispatchers, the listener should be up be

Listener/Database shutdown sequence

Should the databases be shutdown before the listener or the other way around. A DBA here (actually at Service Provider) believes that the listener has to be shutdown before the databases in 8i according to some documentation (they can't recall). I am proposing: Set Oracle 817 environment shut

Re: ReInitiate Sequence Number

hi hamid! what about doing it with by twice altering the sequence: /* create the seq */ SQL> create sequence myseq nomaxvalue nocycle nocache order; /* reset the seq */ SQL> select myseq.currval from dual; /* assume that curval = 6 */ SQL> alter sequence myseq maxvalue 6 cycle; /*

RE: Reinitiate Sequence Number

Title: RE: Reinitiate Sequence Number Rajendra,   Thanks allot -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]Sent: Friday, January 31, 2003 12:15 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Reinitiate Sequence Number Search on Google

Re: ReInitiate Sequence Number

lt;[EMAIL PROTECTED]> cc: Subject: ReInitiate Sequence Number Dear List, How can I reinitiate a sequence Instead or dropping & recreating it, I want every night my sequence number reinitiate and start from 1 again. Thanks for your help. Hamid Alavi Office

RE: ReInitiate Sequence Number

Did you use 'recycle'? Sequence must not use 'cache' when attempting to do this. You may also want to check out other options from the Google link posted by Raj. - Kirti -Original Message- Sent: Friday, January 31, 2003 2:39 PM To: Multiple recipients of list

RE: ReInitiate Sequence Number

Kirti, I have to set the maxvalue manually, when I do this still nextval not reset. For example if the maxvalue is 100 and the current value is 500, some how I have to set alter sequence to reach the maxvalue so the next value start from 0 again. This test doesn't do that. -Ori

RE: Reinitiate Sequence Number

Title: RE: Reinitiate Sequence Number Search on Google ... you'll find many hits ... http://www.google.com/search?q=recreating+oracle+sequences&hl=en&lr=&ie=UTF-8&oe=UTF-8 Raj __ Rajendra Jamadagni      MI

RE: ReInitiate Sequence Number

Hamid, Here is one way: SQL> select myseq.currval from dual; CURRVAL -- 4 SQL> alter sequence myseq nocache; Sequence altered. SQL> alter sequence myseq maxvalue 4 cycle; Sequence altered. SQL> select myseq.nextval from dual; NEXTVAL -- 1 SQ

RE: ReInitiate Sequence Number

Kirti, I test it but it doesn't work! -Original Message- Sent: Friday, January 31, 2003 11:13 AM To: Multiple recipients of list ORACLE-L Hamid, Here is one way: SQL> select myseq.currval from dual; CURRVAL -- 4 SQL> alter sequence myseq nocache

RE: ReInitiate Sequence Number

Kirti, I test it it doesn't work. -Original Message- Sent: Friday, January 31, 2003 11:09 AM To: [EMAIL PROTECTED] Cc: Hamid Alavi Hamid, Here is one way: SQL> select myseq.currval from dual; CURRVAL -- 4 SQL> alter sequence myseq nocache; Seque

RE: ReInitiate Sequence Number

Hamid, Here is one way: SQL> select myseq.currval from dual; CURRVAL -- 4 SQL> alter sequence myseq nocache; Sequence altered. SQL> alter sequence myseq maxvalue 4 cycle; Sequence altered. SQL> select myseq.nextval from dual; NEXTVAL --

ReInitiate Sequence Number

Dear List, How can I reinitiate a sequence Instead or dropping & recreating it, I want every night my sequence number reinitiate and start from 1 again. Thanks for your help. Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentia

RE: strange behaviour of sequence

Hi Oraora, I try the same thing like you did, SQL> create sequence testseq start with 1; Sequence created. SQL> create table testtab (text char(10)); Table created. SQL> begin 2for i in 1 .. 5 loop 3 insert into testtab values('BREAD'||testseq.nextval);

Re: Re: Strange behaviour of sequence

thanks a lot everybody. i have changed the sequence to NOCACHE. Regards, Prem Khanna J. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: oraora oraora INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California

Re: strange behaviour of sequence

hi j.p.! try with SQL > create sequence testseq start with 1 NOCACHE ORDER; regards daniel oraora oraora wrote: > > Guys, > > one of my developers is using sequence to auto-increment the value > of a column while inserting. > > he has created a sequence like

RE: strange behaviour of sequence

>Guys, > >one of my developers is using sequence to >auto-increment the value >of a column while inserting. > >he has created a sequence like this. > >SQL > create sequence testseq start with 1; > >and then uses a INSERT statement as below in a JSP. >

RE: strange behaviour of sequence

Hi Jp The gaps in sequences can most likely be attributed to the sequence being cached. The cache option (default) pre-allocates a set of sequence numbes and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used

RE: strange behaviour of sequence

Probably you use the (default) 'cache 20' and shut down the database between the 'BREAD4' and 'BREAD21' insert. Or you use OPS/RAC, which cause each instance to cache this '20' numbers. This can be avoided by using the 'nocache' option, but then y

RE: strange behaviour of sequence

Ther is something called 'cache' in a sequence definition which has a default value of 20. Which means 20 values of the sequence will be pre-fetched and kept in the memory. this is done to avoid latching issues. when u shutdown the instance the pre-fetched values are lost, and th

RE: strange behaviour of sequence

Jp: Is the sequence cached? Check out Note:62002.1 on Metalink - "Applications which use Oracle sequences which have the CACHE option enabled will often 'skip' values. This article discusses the cache option, why numbers can be lost and how to minimize this occurrance." You&#

strange behaviour of sequence

Guys, one of my developers is using sequence to auto-increment the value of a column while inserting. he has created a sequence like this. SQL > create sequence testseq start with 1; and then uses a INSERT statement as below in a JSP. insert into testtab values ('BREAD'||te

RE: Sort (Collating Sequence)

Title: Sort (Collating Sequence) Laura,   The way that I have always handled this was to create another column in the table, and populate it with a before insert/update trigger.   In your case, I would create a VENDOR_SORT column and move the vendor_name column into this column.  I have

Sort (Collating Sequence)

Title: Sort (Collating Sequence) I have a question concerning a situation with our ORDER BY clauses.  We have a vendor table which allows the user to input any case.  Therefore we have 'Vendor' and 'VENDOR'.  When using the ORDER BY clause it sorts VENDOR first and then

RE: Row cache locks on INSERTs with a sequence

Title: RE: Row cache locks on INSERTs with a sequence Jonathan, The inserts were into a staging table.   After the staging table was loaded into the live tables, they were using delete and not truncate to flush the staging table.   So of course the table and index were acquiring an

Re: Row cache locks on INSERTs with a sequence

Just as a side-line observation - when I realised that the problem should have been with the sequence, I set up a small test on a multi-CPU box to run multiple concurrent copies of: begin for i in 1..10 loop insert into t1 values (test_seq.nextval); end loop

RE: Row cache locks on INSERTs with a sequence

Title: RE: Row cache locks on INSERTs with a sequence If I stated dc_segments in my original post, I apologize, I *did* mean to say dc_sequences.   At any rate, as usual, the problem was poor application code. The row cache lock no longer shows up as one of the top 5 wait events per

Re: Row cache locks on INSERTs with a sequence

Correct - I was sure that I had a set of test results to prove my point, but I didn't - so I've just run a series of tests on enqueue and buffer busy waits on an 8.1.7.4 system and you are absolutely right - the seconds_in_wait does not reset as the wait completes. Thanks, Jonathan Lewis

RE: Row cache locks on INSERTs with a sequence

iple recipients of list ORACLE-L On the information as supplied you are correct, the sequence does seem to be the most likely problem. It has occurred to me, though, that the original poster showed us a couple of lines from v$session_wait - without showing the STATE (or is it STATUS) column. It is

Re: Row cache locks on INSERTs with a sequence

On the information as supplied you are correct, the sequence does seem to be the most likely problem. It has occurred to me, though, that the original poster showed us a couple of lines from v$session_wait - without showing the STATE (or is it STATUS) column. It is a common error to assume that

RE: Row cache locks on INSERTs with a sequence

You are nice person. I still think the problem was mainly a sequence problem. regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 12/20/02 9:43 PM And I was unlucky :( Cache id 13 is dc_sequences on 8i and 9i versions of oracle, so I guessed shared pool

RE: Row cache locks on INSERTs with a sequence

And I was unlucky :( Cache id 13 is dc_sequences on 8i and 9i versions of oracle, so I guessed shared pool shortage, if latch waits did not point to SQ enqueue... - Kirti -Original Message- Sent: Friday, December 20, 2002 3:39 PM To: Multiple recipients of list ORACLE-L It matches t

RE: Row cache locks on INSERTs with a sequence

Title: RE: Row cache locks on INSERTs with a sequence Thanks for the replies.   And, good guess Jonathan. We've already made some changes to the stored procedure implementing the INSERT statement. Upon investigation, I found that the table involved is used as a staging table

Re: Row cache locks on INSERTs with a sequence

It matches to the cache# in v$rowcache. I have to say that I seem to have got to a suitable conclusion by mistake. The OP quoted cache id 13, and said it references dc_segments - hence my suggestion about small extents. In fact cache id = 2 is dc_segments, and cache id = 13 is dc_sequences (at

RE: Row cache locks on INSERTs with a sequence

Title: RE: Row cache locks on INSERTs with a sequence Let me guess - they did not say sorry to you. -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday, December 20, 2002 12:21 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Row cache locks on

RE: Row cache locks on INSERTs with a sequence

Title: RE: Row cache locks on INSERTs with a sequence It looks like a blame game!   You mentioned many changes: truncate, dropping the use of sequence,  etc.   So which one was it?   :)   Waleed -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday

RE: Row cache locks on INSERTs with a sequence

-- > 29 row cache lockcache id 13 mode 0 request5 > 105 row cache lockcache id 13 mode 0 request5 > >The offending SQL statement is an INSERT of the following form: > >INSERT INTO TABLE (A,B,C,D...) VALUES (:b1, :b2, :b3, SEQUENCE.NEXTVAL,..) > >

Re: Row cache locks on INSERTs with a sequence

an INSERT of the following form: > >INSERT INTO TABLE (A,B,C,D...) VALUES (:b1, :b2, :b3, SEQUENCE.NEXTVAL,..) > >The sequence in question has it's cache value set to the default of 20. > >The developers keep insisting that it's a shared pool issue. I've >re

RE: Row cache locks on INSERTs with a sequence

Title: Row cache locks on INSERTs with a sequence You are waiting on locks for the dictionary cache dc_sequences (cache id = 13).  Increasing how many sequences are cached may not help.  If sequence caching were the problem, you would seen waits for latch free event (for sequence cache

RE: Row cache locks on INSERTs with a sequence

Title: Row cache locks on INSERTs with a sequence It looks like sequence generation & caching rate is slower than your insertion rate.   Waleed -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]]Sent: Friday, December 20, 2002 9:45 AMTo: Multiple recipients of

Row cache locks on INSERTs with a sequence

Title: Row cache locks on INSERTs with a sequence We are continually seeing sessions hanging on row cache locks, which in turn appear to be on dc_segments:  SID EVENT  P1TEXT P1 P2TEXT   P2 P3TEXT    P3 - - -- -- -- -    29

RE: RE: Re[2]: sequence numbers

acle DBA > > Keep yourself well oiled with life, laughter, new ideas and action. > > Otherwise you will rust out. _Anonymous > > > > > > -Original Message- > > Sent: Thursday, October 10, 2002 7:34 PM > > To: Multiple recipients of list ORACL

Re:RE: Re[2]: sequence numbers

queries will fly on a table that can't be > built. You > > can't have 37 columns in a primary key. Date really isn't an > acceptable > > name for a column. > > > > April Wells > > Oracle DBA > > Keep yourself well oiled with life, laugh

RE: Re[2]: sequence numbers

But the DOCUMENTATION says 8-0 April Wells Oracle DBA Keep yourself well oiled with life, laughter, new ideas and action. Otherwise you will rust out. _Anonymous -Original Message- Sent: Friday, October 11, 2002 10:20 AM To: Multiple recipients of list ORACLE-L it's all in the

RE: Re[2]: sequence numbers

a primary key. Date really isn't an > acceptable > > name for a column. > > > > April Wells > > Oracle DBA > > Keep yourself well oiled with life, laughter, new ideas and action. > > Otherwise you will rust out. _Anonymous > > > > >

RE: Re[2]: sequence numbers

ed with life, laughter, new ideas and action. > Otherwise you will rust out. _Anonymous > > > -Original Message- > Sent: Thursday, October 10, 2002 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Hi Dick, > > I have to disagree with you here.

RE: Re[2]: sequence numbers

gt; > April Wells > Oracle DBA > Keep yourself well oiled with life, laughter, new ideas and action. > Otherwise you will rust out. _Anonymous > > > -Original Message- > Sent: Thursday, October 10, 2002 7:34 PM > To: Multiple recipients of list ORACLE-L > &g

RE: Re[2]: sequence numbers

ORACLE-L Hi Dick, I have to disagree with you here. Particularly in the case where this sequence will see any sort of concurrency, from multiple concurrent sessions accessing it. This is due to the serialization on the SQ enqueue. This will cause far worse scalability issues than any I/O.

Re: Re[2]: sequence numbers

Hi Dick, I have to disagree with you here. Particularly in the case where this sequence will see any sort of concurrency, from multiple concurrent sessions accessing it. This is due to the serialization on the SQ enqueue. This will cause far worse scalability issues than any I/O. Not that I

Re: sequence numbers

There is no read penalty, but there is a indirect write penalty. Every nextval operation will start a recursive transaction that will generate redo and that needs to be flushed to disk. Anjo. [EMAIL PROTECTED] wrote: > > Actually there is no IO penalty since Oracle has to treat the se

Re[2]: sequence numbers

Actually there is no IO penalty since Oracle has to treat the sequence just like any table with the old LRU algorithm. I have several sequences with a cache of 0 and they perform as well as those with a cache value. The big difference is when you shut down the database and all of those cached

Re: sequence numbers

I think that you will have an update to the sequence number EVERY time instead of every 20 times. That's mean I/o for every nextval.   Yechiel AdarMehish - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Thursday, October 10, 2

Re: sequence numbers

tiple recipients of list ORACLE-L Sent: Wednesday, October 09, 2002 8:54 AM Subject: sequence numbers I have been given create scripts for sequences to be used in tables that will be loaded via bulk loads.  How huge is the potential performance hit if I take out the cache 20?  

RE: sequence numbers

his exercise once to allay developers and mgrs fears that using a sequence would cause problems down the road when the sequence 'ran out'. We standardized on a 12 digit integer for primary keys. 12 digits allows a maximum key value of (10 * 10^11)-1, or . If you were ins

RE: sequence numbers

Wells @csedge.com> cc: Sent by: rootSubject: RE: sequence

RE: sequence numbers

tion. Otherwise you will rust out. _Anonymous -Original Message- Sent: Wednesday, October 09, 2002 10:54 AM To: Multiple recipients of list ORACLE-L If the sequence is not cached then Oracle has to generate a new sequence number for every record inserted (CPU cycles). NOORDER is the

RE: sequence numbers

Order and noorder options are relevant only for the OPS/RAC configurations because that is the only way that the sequence numbers may be returned out of order. If the sequence is not cached, then you'll suffer a logical read and a physical write each time you query the sequence. What will

RE: sequence numbers

well oiled with life, laughter, new ideas and action. Otherwise you will rust out. _Anonymous -Original Message- Sent: Wednesday, October 09, 2002 10:54 AM To: Multiple recipients of list ORACLE-L If the sequence is not cached then Oracle has to generate a new sequence number for every

Re: sequence numbers

If the sequence is not cached then Oracle has to generate a new sequence number for every record inserted (CPU cycles). NOORDER is the default so that won't slow you up. If you're doing bulk loads why not cache the sequence numbers? Is it important that there be no gaps? Even wi

sequence numbers

I have been given create scripts for sequences to be used in tables that will be loaded via bulk loads.  How huge is the potential performance hit if I take out the cache 20?   April Wells Oracle DBA There is neither good nor bad, but thinking makes it so. -Shakespeare CSUA 2002: Imagine t

RE: Lock on a sequence

SQ enqueue for each sequence. Increasing the cache size will help. Anjo. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002 9:14 PM > For incrementing sequence number, Oracle has to use an SQ enqueue latc

Re: Lock on a sequence

Just want to be picky. It is the SQ enqueue not the latch. There is an SQ enqueue for each sequence. Increasing the cache size will help. Anjo. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 23, 2002

RE: Lock on a sequence

For incrementing sequence number, Oracle has to use an SQ enqueue latch. To minimize the need for this latch, you can increase the cache size for the Sequences. Additionally, you can also consider KEEPing the Sequence number in Shared pool using dbms_shared_pool.keep() package procedure

Lock on a sequence

trying to query a sequence. select my.sequence_list.next_value from dual Does anyone know a possible cause for a sequence to be locked? Does anyone know how to lock a sequence? Thanks ahead of time. Tim Boles DBA Lockheed Martin Information Systems -- Please see the official ORACLE-L FAQ

RE: Sequence number generation

So far we haven't gotten into a problem of locking, there are about 15-20 users of the system. -Original Message- Sent: Thursday, July 18, 2002 8:28 PM To: Multiple recipients of list ORACLE-L Subject: RE: Sequence number generation I really don't see where t

  1   2   3   >