Undo issue

In Undo Segments there are three types of extents, they are

Unexpired Extents – Undo data whose age is less than the undo retention period.
Expired Extents – Undo data whose age is greater than the undo retention period.
Active Extents – Undo data that is part of the active transaction.

-----------------------------------
Check the overall status for undos.
-----------------------------------

SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB
from dba_undo_extents group by tablespace_name, status;

TBS STATUS  GB

UNDO_TDS EXPIRED  5.95526123046875
UNDO_TDS UNEXPIRED 0.0001220703125

--------------------------

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"  FROM v$undostat;

UNDO_BLK_PER_SEC
1241.68

----------------------
Undo Blocks per Second
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",       
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       
       ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" 
FROM (       
      SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a,v$tablespace b,dba_tablespaces c         
       WHERE c.contents = 'UNDO'         
         AND c.status = 'ONLINE'         
         AND b.name = c.tablespace_name           
         AND a.ts# = b.ts#       
      ) d,v$parameter e,v$parameter f,( SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec         
                                          FROM v$undostat 
                                      ) g WHERE e.name = 'undo_retention'  AND f.name = 'db_block_size'

          
ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC  OPTIMAL_UNDO_RETENTION_SEC
-------------------  ------------------  --------------------------
6108      900       630

----------------------
Optimal Undo Retention
----------------------


SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",       

ACTUAL_UNDO_SIZE_MB  UNDO_RETENTION_SEC<]" 
    FROM (        SELECT SUM(a.bytes) undo_size         
        FROM v$datafile a, v$tablespace b, dba_tablespaces c 
       WHERE c.contents = 'UNDO'         
 tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from 
SIZE_MB FREE_MB  NEEDED_UNDO_SIZE
-------------------  ------------------  -----------------
6108      900      8730.5625


------------------------------------------------------
Calculate Needed UNDO Size for given Database Activity
------------------------------------------------------


select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/


TBS  SIZE_MB FREE_MB %USED
---  ------- ------- -----

UNDO_TDS 6108   10  99

1. What are the current UNDO settings (including UNDERSCORE settings)? 

SET PAGESIZE 900 
COL NAME FORMAT A32 
COL VALUE FORMAT A40 
SPOOL UNDOPARAMS.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Internal AUM settings */ 
select nam.ksppinm NAME, val.KSPPSTVL VALUE 
from x$ksppi nam, x$ksppsv val 
where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter' ) ) 
order by 1; 
SPOOL OFF 

2. What are the various statuses for Undo Extents? 
SELECT DISTINCT STATUS, SUM(BYTES)/1024/0124 "BYTES (MB)", COUNT(*) 
FROM DBA_UNDO_EXTENTS 
GROUP BY STATUS; 

3. Please provide the TUNED_UNDORETENTION details from V$UNDOSTAT. 
Note that the nls_date_format is needed so that the time is displayed. 

ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI:SS'; 
SET PAGESIZE 900 
SET LINESIZE 255 
SPOOL UNDOSTAT.TXT 
/* Database identification */ 
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database; 
select * from V$version where banner like 'Oracle Database%'; 
/* Tuned Undo Retention */ 
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT) FROM V$UNDOSTAT; 
SELECT BEGIN_TIME, END_TIME, TUNED_UNDORETENTION, MAXQUERYLEN, MAXQUERYID, NOSPACEERRCNT, EXPSTEALCNT, UNDOBLKS, TXNCOUNT FROM V$UNDOSTAT; 
SPOOL OFF 

4. For the UNDO TableSpace, what is the RETENTION GUARANTEE? 
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO'; 

5. How much free space is in the UNDO TableSpace? 
SELECT SUM(BYTES)/1024/1024 "BYTES (MB)" FROM dba_free_space WHERE tablespace_name='&UNDOTBS'; 

6. For the Data Files used for Undo, what is the autoextend setting? 
COL AUTOEXTENSIBLE FORMAT A14 
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='&UNDOTBS'; 

7. set pagesize 50 
set linesize 100 
column UNXPSTEALCNT heading "# Unexpired|Stolen" 
column EXPSTEALCNT heading "# Expired|Reused" 
column SSOLDERRCNT heading "ORA-1555|Error" 
column NOSPACEERRCNT heading "Out-Of-space|Error" 
column MAXQUERYLEN heading "Max Query|Length" 
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, 
UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, MAXQUERYLEN, TUNED_UNDORETENTION 
from gv$undostat 
order by inst_id, begin_time;