OraFAQ Forum: Server Administration » UNDO Tablespace and ORA-01555

Home » RDBMS Server » Server Administration » UNDO Tablespace and ORA-01555

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNDO Tablespace and ORA-01555 [message #117611]

Thu, 28 April 2005 09:21

Zakkhalid
Messages: 47
Registered: April 2005

Member

Hi All,

I'm running db with undo tablespace with undo_rentention of 14400. I have 202 rollback segments. SMON brings these online when required.., however I get
ORA-01555: snapshot too old: rollback segment number 9 with name "_SYSSMU9$"

Shouldn't smon bring more segments online to get round this, or should I up the storage parameters of the rollback segs.

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #117626 is a reply to message #117611]

Thu, 28 April 2005 10:04

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Hi,

Looks like you have queries running longer than 4 hours in your system. You need to tune these queries or increase the value of UNDO_RETENTION until these problems are resolved.

BTW: Oracle 10g will increase this parameter automatically as long as you don't have any space pressure in the UNDO tablespace.

Best regards.

Frank

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #117627 is a reply to message #117626]

Thu, 28 April 2005 10:10

Zakkhalid
Messages: 47
Registered: April 2005

Member

Thanks for the reply Frank. I guess I'll have to look at the query again!! Razz

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118429 is a reply to message #117627]

Thu, 05 May 2005 03:30

_simma_dba
Messages: 34
Registered: November 2003

Member

Yes you should look at your query. Maybe you have some cursor, and frequently use of commit in your transaction.

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118448 is a reply to message #118429]

Thu, 05 May 2005 06:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Maybe you have some cursor, and frequently use of commit in your transaction.

That will be hurting to the database.

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118456 is a reply to message #118448]

Thu, 05 May 2005 07:10

tycoonajayverma
Messages: 17
Registered: May 2005
Location: b'lore

Junior Member

hi all

yes everybody is write u look ur query which taking so much time to get completed find the query from V$sqlarea with join of V$session. or post it here we will have a look to ur query.

thanks

Regards,
Ajay Verma.
DBA

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118460 is a reply to message #118448]

Thu, 05 May 2005 07:29

_simma_dba
Messages: 34
Registered: November 2003

Member

Mahesh Rajendran wrote on Thu, 05 May 2005 06:04

>>Maybe you have some cursor, and frequently use of commit in your transaction.

That will be hurting to the database.

Maybe my english is not that good but I was talking about Fetch across commit. I had that problem and I solve it by commiting not afther every row in transaction but afther some 10000 rows.

You can find everything in http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:546822742166

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118468 is a reply to message #118460]

Thu, 05 May 2005 08:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

My apologies.
I Understood it as you are advocating for COMMITING freqently.
>> I solve it by commiting not afther every row in transaction
Is much clear.

Report message to a moderator

Re: UNDO Tablespace and ORA-01555 [message #118470 is a reply to message #118468]

Thu, 05 May 2005 08:24

_simma_dba
Messages: 34
Registered: November 2003

Member

NP as I said "My english" Rolling Eyes

Report message to a moderator

Previous Topic:	hotbackup taking tablespace offline
Next Topic:	Does Delete save space?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 04:20:42 CST 2025