ORA1555 and ORA30036

[Former Member]

Hi,

How can I change the undo_retention of the undo segment? Do we need a downtime for the change? Currently our undo parameters shows:

Name Type Value

-

---

undo_management string AUTO

undo_retention integer 650

undo_suppress_errors boolean FALSE

undo_tablespace string PSAPUNDO

We are always getting an ORA1555 error when we do a client copy. Does it makes sense to incerease the undo_retention? If yes then whats the recommended size?

We are also getting an ORA30036. How can we solve this?

Thanks

kbas

[Former Member · Accepted solution]

Hi Bas,

undo_retention can be changed on the fly,no downtime is needed.

Commands related are

SQL> show parameter undo_retention;(shows result in this format)

NAME TYPE VALUE

-

---

-

---

-

---

undo_retention integer 21600

Command to change value.

SQL> alter system set undo_retention=<value>;

If undo_retention is increased then we should also increase undo tablespace.

To avoid ORA-1555 we can increase undo_retention.

As from your values i believe undo_retention is only 650 seconds,may be this can be increased to say 1-2 hours .If the above ora error is occurring frequently in your system then you increase the retention.However if the error occurs only during client copy then increase it before client copy and then reduce again.But before reducing ensure that there are no long running jobs in the system.

Hope the above explanation clarifies you.

Award points if above information is useful.

Padmaja

[Former Member · Accepted solution]

Thanks Padmaja,

The copy is now running (for 2 days already). I have already inceased the PSAPUNDO from 20GB to 40GB. Can I update the undo_retention while the copy is still running?

rgds,

kbas

[Former Member · Accepted solution]

Hi Bas,

Yes you can increase.

Padmaja

[Former Member · Accepted solution]

Thank you very much Padmaja. Full points to you.

cheers,

kbas

[Former Member · Accepted solution]

Hi Bas,

Thanks

Padmaja

[Former Member]

Hi Bas,

ORA-30036: unable to extend segment by string in undo tablespace 'string'

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.

Add datafiles to the tablespace and it should solve the problem.

Padmaja.