cc:
Sent by: Subject: Re: Snapshot too old in undo
tablespace in 9i?
[EMAIL PROTECTED
Stephen,
Tim's statement is correct, but can be construed incorrectly if you read it and think
of TEMP segments. AUM still uses undo segments (same basic structure as rollback
segments). However, one of the space management steps is to allow an undo segment to
'steal' extents from another undo
cc:
Sent by: Subject: Re: Snapshot too old in undo
tablespace in 9i?
[EMAIL PROTECTED
Abraham,
Setting the retention time may not solve the problem. One of the ways that an ORA-1555
can be triggered is when the transaction table slot is overwritten. This is caused by
having many small, serial transactions in the database while the export is running.
In each undo segment (or
retention time --- set it for a couple of hours longer than you think the
export will take.
Guerra, Abraham
Thanks.
Abraham
-Original Message-
Sent: Tuesday, August 05, 2003 12:35 PM
To: Multiple recipients of list ORACLE-L
retention time --- set it for a couple of hours longer than you think the
export will take.
:
Subject: Re: Re: Snapshot too old
during stress test... how to avoid
not updating the data dictionary.
From: Ganesh Raja [EMAIL PROTECTED]
Date: 2003/06/05 Thu AM 10:09:52 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Re: Snapshot too old during stress test... how to avoid
Snapshot Too Old Maybe caused bcos of Delayed Block
Thu AM 03:25:36 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Snapshot too old during stress test... how to avoid
Speaking of this trick with a txn in each RBS, I've got a shell script
on my website (http://www.evdbt.com/tools.htm) that does just
recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: Re: Snapshot too old during stress test... how to avoid
Snapshot Too Old Maybe caused bcos of Delayed Block Cleanout Problems.
Now this Bock was updated an Hour ago and u are now selecting it.. It
finds that there are ITL Entries
E-L [EMAIL PROTECTED]
cc:
Subject: Re: Snapshot too old during stress test... how to avoid
Try removing optimal setting, and shrinking RBS to the min extents (or
even below) before running
your tests.
- Kirti
--- Garry Gillies [EMAIL PROTECTED] wrote:
From
The rbs does not shrink to optimal when the transaction commits. The 2nd
transaction to 'find' that the rbs has extended beyond optimal will
cause the rbs to shrink.
--
Daniel W. Fink
http://www.optimaldba.com
Thomas Day wrote:
With optimal, the rollback segment shrinks back to its optimal
recipients of list ORACLE-L [EMAIL PROTECTED]
Sent by: rootcc:
Subject: Re: Re: Snapshot
too old during stress test... how to avoid
06/05/2003 08:31
Is the error only occurring when you do the create table as select? Are
any other processes modifying data in the source table?
What is the db_block_size?
[EMAIL PROTECTED] wrote:
nope. My process is rather simple.
A flag comes in... either we do a create table as, drop the old table, and
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 05 June 2003 18:40
To: Multiple recipients of list ORACLE-L
Subject: Re: Re: Snapshot too old during stress test... how to avoid
nope. My process is rather simple.
A flag comes in... either we do a create table as, drop the
old table
for some brief queries.
From: Tim Gorman [EMAIL PROTECTED]
Date: 2003/06/05 Thu AM 03:25:36 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Snapshot too old during stress test... how to avoid
Speaking of this trick with a txn in each RBS, I've got a shell script on my
From memory (of a course attended looong ago),
Oracle recommends one rollback segment for every
three to four users.
Four rollback segments between thirty six processes
does seem a little mean.
Garry
[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
04/06/03 13:59
Please respond to ORACLE-L
Try removing optimal setting, and shrinking RBS to the min extents (or even below)
before running
your tests.
- Kirti
--- Garry Gillies [EMAIL PROTECTED] wrote:
From memory (of a course attended looong ago),
Oracle recommends one rollback segment for every
three to four users.
Four
: Snapshot too old during stress test... how to avoid
Try removing optimal setting, and shrinking RBS to the min extents (or
even below) before running
your tests.
- Kirti
--- Garry Gillies [EMAIL PROTECTED] wrote:
From memory (of a course attended looong ago),
Oracle recommends one rollback
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Snapshot too old during stress test... how to avoid
Try removing optimal setting, and shrinking RBS to the min extents (or
even below) before running
your tests.
- Kirti
--- Garry
2.
Fix: Increase Rll Back segments
1.
Why: Oracle RDBMS tries to provide "Read consistent " viiew of the data (
changed or clean ) at the "statement level" read consitency and "transaction
level" read consistency
This
isone of the whys- an Oracle DBA's makeshis living in their
AK
Take a look at this. It explains it better than I could.
http://home.clara.net/dwotton/dba/snapshot2.htm
http://home.clara.net/dwotton/dba/snapshot2.htm
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, March 28, 2003
AK...
this happens with LONG queries too...
this means that you do not have enough rollback
segments...
or...
that they are very small...
HTH
JL
--- AK [EMAIL PROTECTED] wrote:
why a select statement can give snapshot too old
erorr .
there is no update , yes but its a long running
query
Jackson,
As you have discovered , the issue is that other transactions are
overwriting your read consistent view of the tables.
Options could include
1) Running the export at a quite time (sounds like you have tried that but
with only partial suucess)
2) Taking a direct export which reduces the
You might try running hot backups at the OS level instead. It sounds
like your export is just taking too long. To get it to work with active
users on board you might have to increase your rollback segments to
truly ridiculous levels especially if you have large batches running
while the export
I would echo a previous post that you can't backup a database with the
export utility. I suspect you get your error because you are using
consistent=y in the export. The database is trying to give you data as of
the time you started the export. The fact that you are getting the snapshot
too
In addition to the other comments about export not being a backup, let me
add a few things. Managing the trade off between many rollback segments
(good for oltp performance) and large rollback segments (necessary to avoid
1555) is often a tough one. As somebody else said, you can optimize
Stephen,
My understanding is having consistent=y uses no rollback, since the changes
occurring
during the export are not being captured in the export dump.
on few occasions i've still got the spurious ora-1555(snap shot too
old)error on exporting a 80GB highly
transactional database which
I'm certainly not an Oracle internals expert. My understanding of ANY
consistent set of data is provided by the rollback segments. It goes
something like this:
A select is run
The database notes the SCN where the select started
Consistent data is given to the query by pulling data from the
Your understanding of consistent=y is not correct. With consistent=yes,
export needs to be able to get all the rollback generated since the start
of the export until its complete, hence consistent=y typically has prohibitively
high rollback requirements. Please see my other mail for some more
It would have to be using rollback segments. That's where it would read the
consistent view of data blocks from that had been changed since the start of the
export.
[EMAIL PROTECTED] 02/21/03 01:14PM
Stephen,
My understanding is having consistent=y uses no rollback, since the changes
Patrick,
A code around I've used is to periodically close and reopen the cursor.
Logic is as follows
define
v_loop_size number := 5000;
v_prev_unique_id number := 0;
v_hold_unique_id number := 0;
main_cur (c_start_unique_id in number) is
select ...
from ...
where
Patrick - Do any of these jobs update the same tables? Or do any jobs read a
table that other jobs are updating?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, January 07, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L
Dear,
No, the different jobs use different tables.
As I already mentioned I did find in 5 programs something like this :
Cursor c1 is select * from x where id = xx;
For c1rec in c1 loop
Blabla
Blabla
Update table x set id = NULL where id = xx;
Commit;
End loop;
This is for
Patrick - Just an idea for you, given that the jobs don't share tables.
Sounds as if you may have fetch across commit problem like Dick mentioned.
The best solution would be to fix the programs. A stopgap method in the
meantime would be to assign each job to its own rollback segment so the
blocks
no , i do not .
Thanks
DBAndrey
* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Tue, May 28, 2002 4:53 AM
To: Multiple recipients of list ORACLE-L
Hello Andrey,
Do you use autonomous transaction?
Monday, May 27, 2002, 7:53:19 PM, you wrote:
AB
How about DML before (ie before your query starts)?
If there are massive amount of blocks to be cleaned
out, this can lead to ora-1555
hth
connor
--- Andrey Bronfin [EMAIL PROTECTED] wrote:
I meant , no one runs a DML against those tables
during the running of the
program.
DBAndrey
On Mon, 27 May 2002, Nirmal Kumar Muthu Kumaran wrote:
Increase rollback segment size for this transaction and make sure
that the transaction will use the huge rollback segment
I don't think that solution is correct. This is a common
misconception about snapshot too old. Assigning your
I meant , no one runs a DML against those tables during
the running of the program.
Right. If there is no DML while your program is running, then the snapshot
error is due to DML that was run earlier. The solution is simple.
In the programs that do large batch inserts or updates on these
Title: RE: snapshot too old error - strange
Increase rollback segment size for this transaction and make sure that the
transaction will use the huge rollback segment
hth
nirmal
-Original Message-
From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]]
Sent: Mon, May 27, 2002 15:53
Hi Andrey,
Are there any LOB columns in the tables ? I read something on Metalink
about bugs on ORA-01555 messages (followed by a ORA-22924), on tables
with LOBs ...
HTH, Remco
-Oorspronkelijk bericht-
Van: Andrey Bronfin [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 27 mei 2002 14:53
Check size of your rollback segs Vs the job of selecting from a big table to
insert into another big table -might have to increase size of segs.
-Original Message-
Sent: Monday, May 27, 2002 5:03 PM
To: Multiple recipients of list ORACLE-L
Hi Andrey,
Are there any LOB columns in the
no , no lobs.
thanks !
DBAndrey
* 03-9254520
* 058-548133
* mailto:[EMAIL PROTECTED]
-Original Message-
Sent: Mon, May 27, 2002 4:03 PM
To: Multiple recipients of list ORACLE-L
Hi Andrey,
Are there any LOB columns in the tables ? I read something on Metalink
about bugs on
2 huge tables A and B
Now , NO ONE carries a DML agains A or B.
If no one ever does DML against those two tables, how did they end up having
so many rows?
Do you run large batch update or insert operations against these tables from
time to time?
--
Please see the official ORACLE-L FAQ:
Greg Moore wrote:
2 huge tables A and B
Now , NO ONE carries a DML agains A or B.
If no one ever does DML against those two tables, how did they end up having
so many rows?
Do you run large batch update or insert operations against these tables from
time to time?
One additional
Seema Singh wrote:
Hi
At time of export i am getting following error
EXP-8: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 6 with name R03 too
small
Please suggest
Thanks
-Seema
Mladen, any idea ?
--
Regards,
Stephane Faroult
Oriole Ltd
--
Stephane...
You're mean...
:-)
-Original Message-
Sent: Thursday, January 17, 2002 6:25 AM
To: Multiple recipients of list ORACLE-L
Seema Singh wrote:
Hi
At time of export i am getting following error
EXP-8: ORACLE error 1555 encountered
ORA-01555: snapshot too old:
Oh, no! Not Gogala M Laden!
-Original Message-
Stephane...
You're mean...
:-)
-Original Message-
Sent: Thursday, January 17, 2002 6:25 AM
To: Multiple recipients of list ORACLE-L
Seema Singh wrote:
Hi
At time of export i am getting following error
EXP-8: ORACLE
There are several ideas, but I'm working on my charming and
likable personality. I must do something before the duhveleopers
organize a lynch mob and burn me at stake.
-Original Message-
Sent: Thursday, January 17, 2002 11:11 AM
To: Multiple recipients of list ORACLE-L
Stephane...
Don't be afraid. I'm not hungry today.
-Original Message-
Sent: Thursday, January 17, 2002 11:31 AM
To: Multiple recipients of list ORACLE-L
Oh, no! Not Gogala M Laden!
-Original Message-
Stephane...
You're mean...
:-)
-Original Message-
Sent: Thursday, January
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 16, 2002 23:55
Hi
At time of export i am getting following error
EXP-8: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 6 with name R03
Seema,
The ORA-1555 snapshot too old occurs when export tries to construct a read
consistent view of your data from rollback segments, which has been
overwritten or reused for new transactions.
Other sessions are updating data while the export is running in consistent
mode (using consistent =
52 matches
Mail list logo
