OraFAQ Forum: Server Administration » What happens when UNDO space is full? Transaction takes more time to ERROR?

Home » RDBMS Server » Server Administration » What happens when UNDO space is full? Transaction takes more time to ERROR? (Oracle 11g)

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

What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490677]

Wed, 26 January 2011 11:13

sriramkanala
Messages: 9
Registered: December 2005

Junior Member

I have a long running transaction (more than 3 hours), and at the same time other operations are occurring on different tables, using the same UNDO space.

Sometimes we see ORA-30036, but this error occurs very late in the process.

The transaction normally takes 3 hours, but when UNDO space is full, we do not get ORA-30036 upto 8 hours or 9 hours of process.

I am wondering what could be happening in the background, when UNDO space is full, whcih makes the transaction to extend upto 8 hours or 9 hours (pl. note, this transaction gets completed within 3 hours normally).

This is in 11g, UNDO space is managed manually.

Any clues? Thanks in advance

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490678 is a reply to message #490677]

Wed, 26 January 2011 11:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

30036, 00000, "unable to extend segment by %s in undo tablespace '%s'"
// *Cause:   the specified undo tablespace has no more space available.
// *Action:  Add more space to the undo tablespace before retrying
//           the operation. An alternative is to wait until active
//           transactions to commit.

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490679 is a reply to message #490677]

Wed, 26 January 2011 11:39

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

UNDO space is managed manually.

What does this mean?

Regards
Michel

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490682 is a reply to message #490679]

Wed, 26 January 2011 11:50

sriramkanala
Messages: 9
Registered: December 2005

Junior Member

UNDO space is set to a specific value, it's not left to AUTOEXTEND ON.

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490760 is a reply to message #490677]

Thu, 27 January 2011 03:50

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

The error message is returned to the client *after* the rollback has been completed.
A transaction starts at 1pm.
At 3pm it encounters an "out of space".
(Assuming that resumable has not been configured)
It starts rolling back.
The rollback completes at 3:45pm.
The user/client sees an error message at 3:45pm.

You can monitor USED_UREC, USED_UBLK for your transaction and see it increasing between 1pm and 3pm. After 3pm, the count starts falling -- this means that the rollback has begun. The error is presented to the user/client only after the rollback has been completed -- the database has been returned to a consistent state.

Hemant K Chitale

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #490840 is a reply to message #490677]

Thu, 27 January 2011 11:26

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

See : http://hemantoracledba.blogspot.com/2011/01/transaction-failure-when-is-error.html

Report message to a moderator

Re: What happens when UNDO space is full? Transaction takes more time to ERROR? [message #491035 is a reply to message #490840]

Fri, 28 January 2011 09:18

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Be careful with this link, when I went for it I got a warning saying the page was trying to open a file on my workstation.
I don't know why or how, just say it as it happened.

Regards
Michel

[Updated on: Fri, 28 January 2011 09:19]

Report message to a moderator