OraFAQ Forum: Server Administration » Weird issue with ORA-30036

Home » RDBMS Server » Server Administration » Weird issue with ORA-30036

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

Weird issue with ORA-30036 [message #464079]

Tue, 06 July 2010 03:44

ziggy25
Messages: 206
Registered: July 2005

Senior Member

I tried to run a pl/sql program that processed about 2.5m rows. it works without any problems. There was a requirement to process just some of that data so i extracted about 25000 lines from that source file.

When i ran the process it came up with ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'.

- If the undo tablespace is not enough for the 25k lines how was it enough for the 2.5 million lines? The current size of the undo tablespace is 1GB. This is fine as i have run the same process to process the 2.5m lines several times on the development area which has the same undo size.

is it possible that when i run the process the first time it used up all the undo space and the second run could not allocate any undo space? If so, then how is the undo space freed up? (The process has issued all commits where it needs to)

TABLESPACE_NAME               |CUR_USE_MB| CUR_SZ_MB|CUR_PCT_FULL|FREE_SPACE_MB| MAX_SZ_MB|  PCT_FULL
------------------------------|----------|----------|------------|-------------|----------|----------
SYSTEM                        |       185|       400|          46|          215|       400|        46
TABLE_A                       |       633|       680|          93|        32134|     32767|         2
STATS                         |         0|       100|           0|        32767|     32767|         0
SYSAUX                        |       165|       170|          97|        32603|     32768|         1
UNDOTBS1``                    |         7|      1024|           1|         1017|      1024|         1
INDEX_A                       |         1|       100|           1|        32766|     32767|         0

Thanks

Report message to a moderator

Re: Weird issue with ORA-30036 [message #464090 is a reply to message #464079]

Tue, 06 July 2010 04:17

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

Senior Member
Account Moderator

Space is used by:
- blocks currently used
- blocks not used but not expired and you have a guarantee clause on undo tablespace
- blocks not used and expired and suffer a bug that exists in 9i (this is why we ask to ALWAYS post your version and with 4 decimals)

The problem is (most likely) not in the process itself but on the concurrent ones.

Regards
Michel

Report message to a moderator