3

I have a stored proc that do a very large update. Sometimes the job failed with error ORA-30036 Unable to extend segment by 8 in undo tablespace 'undotbs2'

But after a few hours, we reran the job and it completed successfully.

I checked and found undotbs2 already has AUTOEXTENSIBLE set to YES, and size is 3 GB, so I guess the undo tablespace already has pretty decent size there, and has automatic space management already turned on.

My question is, why does it complete successfully after we rerun it? Is it because there were other transactions using undotbs2 at the same time? For this error, Oracle mentions "An alternative is to wait until active transactions to commit.", does "active transactions" refer to other transactions/sql that were happened to run besides the stored proc?

Oracle version is 11.2.0.1.0

Thank you

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
timpham
  • 556
  • 2
  • 7
  • 25
  • 1
    The tablespace/data file is auto-extending, but has it reached it's max size (from `dba_data_files.maxbytes`, or or `dba_tablespaces.maxsize`), and can't go beyond 3GB? Yes, other sessions may be using it (which is what the 'other transactions' is referring to), but it might be worth finding out what else is running and what is using space, before increasing the size limit. – Alex Poole Apr 30 '15 at 16:02

1 Answers1

4

Looks like your UNDO tablespace has reached it MAXSIZE. This can happen if you have a lenghty transaction going on together with other lengthy transactions.

UNDO tablespace is used by Oracle to keep information required for restoring data after your transaction issues a ROLLBACK. That said, its use is dependent on how many active transactions there are at any given moment, and how much information is being changed by each of them.

The resulting usage/size of the tablespace can - as you have experienced - be pretty random.

A solution might be to:

  • increase the MAXSIZE for UNDO tablespace so it can handle the amount of information your lenghty transaction produces
  • modify your implementation, so it issues COMMITS every now and then, so the UNDO information for your lenghty transaction could be freed.
npe
  • 15,395
  • 1
  • 56
  • 55
  • Yes, it is a lengthy transaction. Different sources also mentioned putting in `COMMIT` in between might result in `ORA-01555 snapshot too old` error. Can you explain more on this phenomenon? – timpham Apr 30 '15 at 17:18
  • [Here](http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm) is a good explanation on what happens under the hood, when long-running transactions cause `ORA-01555` errors. – npe Apr 30 '15 at 17:34
  • One more thing - I don't think frequent commits will solve `ORA-01555`, as this one is rather related to long-running queries, or CURSORs that are opened for a very long time. Unless you _close and reopen_ the CURSOR after every COMMIT, just issuing COMMITs won't make `ORA-01555` go away. – npe Apr 30 '15 at 17:41