UNDO tablespace -- useful SQL

Here are useful queries when the database hits with ORA-1555 or ORA-30036 error in UNDO tablespace.

1. UNDO Tablespace sizing information:
---------------------------------------------------------------
Undo Tablespace : APPS_UNDOTS1
Undo Retention_ : .25 Hrs (900)
Max Qry Length_ : 8.72 Hrs (31403)
Max Undo Used__ : 116.97 MB (??)
Undo Size Reqd_ : 54 MB
Undo TabSp Size : 4380 MB
UNEXPIRED Blks_ : 2352 (18 MB)
EXPIRED Blks___ : 11952 (93 MB)
ACTIVE Blks____ : 128 (1 MB)
UNDO Advisory : Enough undo space available in APPS_UNDOTS1 - 4,361 MB

Qureies to get above info:

SQL> show parameter undo
NAME TYPE VALUE
-------------------------- -------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1

---- SQL to check if Expired extents exist
SQL> SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
From note (460481.1): In case no undo space is left, then we try to use unexpired extents (Undo Extent required to honour UNDO_RETENTION). This sometimes results in ORA-1555 errors. Now if you do not have unexpired extents also, then you need to add space to undo tablespace.
If there are no expired extents and we see only Unexpired extents and Active extents then this is most likely Undo sizing issue. In this case, check if Undo Tablespace is correctly sized. Else try reducing value for UNDO_RETENTION.

---- Number of MBytes needed
SELECT ((UR * (UPS * DBS)) + (DBS * 24)) / (1024*1024) AS "MBytes"
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), -- 86400 = seconds in a day
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
MBYTES
------
54

-- If it's not autoextensible, it may hit
-- "ORA-30036 - Unable To Extend Undo Tablespace". To change it:
-- "ALTER DATABASE DATAFILE '&FILE_ID' AUTOEXTEND ON;"
SQL> select file_name, autoextensible, maxbytes/1024 max_KB, bytes/1024 used_KB
from dba_data_files where tablespace_name='APPS_UNDOTS1';

FILE_NAME AUTOEXTENSIBLE MAX_KB USED_KB
--------- -------------- ---------- ----------
/pjyti/oradata/data01/undo01.dbf YES 1843200 2048000
/pjyti/oradata/data01/undo02.dbf YES 1843200 1269760
/pjyti/oradata/data01/undo03.dbf YES 1843200 1167360

---- Total size
SQL> select sum(bytes)/1024 Total_KB from dba_data_files
where tablespace_name='APPS_UNDOTS1';
TOTAL_KB
----------
4487168

---- Free space
SQL> select sum(bytes)/1024 Free_KB from dba_free_space
where tablespace_name='APPS_UNDOTS1';
FREE_KB
----------
4465664

---- Max query length
SQL> select inst_id, max(maxquerylen)
from gv$undostat group by inst_id;
INST_ID MAX(MAXQUERYLEN)
------- ----------------
1 31403

2. Troubleshooting
-----------------------------------------------------------

---- Identify the instance where the error occurs (in RAC):
SQL> SELECT INSTANCE_NUM,TABLESPACE_NAME,SEGMENT_NAME,STATUS
FROM DBA_ROLLBACK_SEGS;
or
SQL> select stat.inst_id, seg.segment_name, seg.tablespace_name
from dba_rollback_segs seg, gv$rollstat stat
where seg.segment_id = stat.usn
and seg.segment_name in ('_SYSSMU7$', '_SYSSMU14$');

INST_ID SEGMENT_NAME TABLESPACE_NAME
------- ------------ ---------------
1 _SYSSMU7$ APPS_UNDOTS1
2 _SYSSMU14$ APPS_UNDOTS1

---- List of active transactions
SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE' ;

---- The UNDO tablespace percent of space in-use now (420525.1)
select
((select (nvl(sum(bytes),0))
from dba_undo_extents
where tablespace_name='APPS_UNDOTS1'
and status in ('ACTIVE','UNEXPIRED')) *100) /
(select sum(bytes)
from dba_data_files
where tablespace_name='APPS_UNDOTS1')
"PCT_INUSE"
from dual;

---- Number of errors during the time period
SQL> select inst_id,
to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,
UNXPSTEALCNT UNEXPIRED_STOLEN, EXPSTEALCNT EXPIRED_STOLEN,
SSOLDERRCNT ORA_1555, NOSPACEERRCNT OUT_OF_SPACE, MAXQUERYLEN
from gv$undostat -- v$undostat does not have inst_id
where begin_time between to_date('04/02/2008 23:30:00','MM/DD/YYYY HH24:MI:SS')
and to_date('04/03/2008 07:00:00','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;
INST_ID BEGIN_TIME UNEXPIRED_STOLEN EXPIRED_STOLEN ORA_1555 OUT_OF_SPACE MAXQUERYLEN
------- ---------- ------------ ----------- ----------- ------------- -----------
1 04/03/2008 05:47 0 0 0 0 1832
1 04/03/2008 05:57 0 0 0 0 2205
1 04/03/2008 06:07 0 0 0 0 478
1 04/03/2008 06:17 0 0 0 0 1293
1 04/03/2008 06:27 0 0 0 0 627
1 04/03/2008 06:37 0 0 1 0 1228
1 04/03/2008 06:47 0 0 0 0 3506
1 04/03/2008 06:57 0 0 0 0 192

. When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.
. If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.
. If the column NOSPACEERRCNT is non-zero, then there is a serious space problem

Currently there are sufficient expired and unexpired space is available for active transactions. No more ORA-1555 errors reported in the alert log file.
After analyzing the session that hits the ORA-1555 error, we see the error has occured for SELECT statment after running hours which is more than undo_retention time.
Conclusion: user needs to tune the select query.

3. Add datafile if it is really needed
-----------------------------------------------------------
ALTER tablespace APPS_UNDOTS1
ADD datafile '/pjyti/oradata/data01/undo04.dbf'
SIZE 200M AUTOEXTEND ON NEXT 20M MAXSIZE 1800M;