In this post I will be discussing various types of Undo extents which can be helpful in determining cause of ORA-30036 i.e unable to extend segment by %s in undo tablespace ‘%s’ and ORA-1555 – Snapshot too old Error
While using Automatic Undo management (Set UNDO_MANAGEMENT initialization parameter to AUTO), we specify Undo_Retention parameter to set the time for which we want undo to be available.
You can query Status column of DBA_UNDO_EXTENTS to check the various kind of extents present.
SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
This would return three values for STATUS field
1)ACTIVE
This means that undo extent is Active and being currently used by a transaction
2)UNEXPIRED
This represents the extents which are required to satisfy the time specified by Undo_retention Initialisation parameter.
3)EXPIRED
These are extents which are not being used by transaction and have crossed the time specified by Undo_retention .
So generally when undo segment space is full then we try to use the Expired extents and if there is no space to either get a new extent or reuse expired extents, then we go and use Unexpired extents. This sometimes lead to ORA-1555 error.
Starting from 10g, a new feature called Retention Guarantee has been introduced. If Retention Guarantee is enabled then you can be assured that you will not be reusing the Unexpired extents but this will mean higher chances of getting ORA-30036 error i.e failing to extend Undo Rollback segment.
So in case you get ORA-30036 , then query DBA_UNDO_EXTENTS to see if there are any expired segments. If there are none and Retention Guarantee is set, then it means that your Undo Tablespace is not properly sized.
You can make use of Undo Advisor available in 10g.
There is one more article on Undo Advisor which can be found Here
You can also manually find required size of Undo by using the below query
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS “Bytes”
FROM (SELECT value AS UR FROM v$parameter WHERE name = ‘undo_retention’),
(SELECT (SUM(undoblks)/SUM(((end_time – begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = ‘undo_tablespace’));
Where
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)