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

[Former Member]

Hi,

I am doing a client import via STMS and encountered ORA-30036 for PSAPUNDO. This is Oracle 10.2.0.4 and size of PSAPUNDO is 20GB. DB size is 180GB and using AUM. PSAPUNDO is set to Autoextend and there were enough disk space in the folder.

Since PSAPUNDO is set to autoextend, why didn't it extend automatically?

SQL> show parameter undo

NAME TYPE VALUE

-

---

-

---

-

---

undo_management string AUTO

undo_retention integer 900

undo_tablespace string PSAPUNDO

Thanks!

[Former Member]

Hi,

The ORA-30036 error is thrown as the database refuses to open.

You can resolve ORA-30036 by first mounting the database and disabling UNDO:

alter system set undo_management = manual scope=spfile;

Next, you must shutdown to resolve ORA-30036 and then startup again using rollback mode and a system rollback segment. Use UNDO tablespace to add files and then use alter system set undo_management=auto scope=spfile; to activate the UNDO once again. To fully resolve ORA-30036, shutdown and restart again.

Hope this helps.

Regards,

Venkata S Pagolu

[Former Member]

You may further need to extend the undo tablespace. See max used space for this tablespace.

Br

Venky

[Former Member]

File name File Id Tablespace name Size(MB) #Blocks Status Relative file number Autoextend Maxsize(MB

Z:\ORACLE\B20\SAPDATA1\UNDO_1\UNDO.DATA1 2 PSAPUNDO 10,000.00 1,280,000 AVAILABLE 2 YES 10,000.00

Z:\ORACLE\B20\SAPDATA1\UNDO_2\UNDO.DATA2 33 PSAPUNDO 9,980.00 1,277,440 AVAILABLE 33 NO 0.00

Maxblocks Increment by User size(MB) User blocks

1280,000 2,560 9,999.93 1,279,992

0 0 9,979.93 1,277,432

1) Since PSAPUNDO is set to autoextend, why didn't it extend automatically when more space is required?

There are 2 datafiles for PSAPUNDO as above and is it because the second datafile UNDO.DATA2 was not set to autoextend?

2) Is it recommended to set Maxsize(MB) to unlimited so that it can extend freely during the client import? Of course I will need to ensure there are enough disk space in Z.

[Former Member]

Hi,

1) Since PSAPUNDO is set to autoextend, why didn't it extend automatically when more space is required?
There are 2 datafiles for PSAPUNDO as above and is it because the second datafile UNDO.DATA2 was not set to autoextend?

Please note that you have specified MAX AUTO EXT limit as 10GB and which has already reached. So, the error.

Second datafile has no auto extension.

2) Is it recommended to set Maxsize(MB) to unlimited so that it can extend freely during the client import? Of course I will need to ensure there are enough disk space in Z.

You can set it to unlimited as you are aware of the underlying filesystem is not full.

You can make second datafile auto ext on using brtools.

Br,

Venky