set echo on
set pages 1000

spool get_undo_details

/*************************************
—- Get DB Block size
*************************************/
show parameter db_block_size;

A Rollback Segment is a database object containing before-images of data written to the database. Rollback segments are used to: Undo changes when a transaction is rolled back. Panacea for an Oracle Applications Database Administrator with Real Application Cluster and Data Guard.

/*************************************
—- Get Undo retention value
*************************************/
show parameter undo retention;

/*************************************
—- Total Undo Tablespace Size
*************************************/

select tablespace_name, autoextensible, sum(bytes)/(1024*1024) size_in_mb
from dba_data_files
where tablespace_name=(select value from v$parameter where name=’undo_tablespace’)
group by tablespace_name, autoextensible
;

/*************************************************
—- No of Undo Data Blocks Generated per second
**************************************************/

select(sum(undoblks))/sum((end_time-begin_time)*86400) from v$undostat;

/*************************************
—- Maximum Undo Consumed
*************************************/

SELECT ROUND(((UR * (UPS * DBS)) + (DBS * 24))/(1024*1024*1024),2) AS “Needed_Undo_Space_in_GB”
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 value AS DBS
FROM v$parameter
WHERE name = ‘db_block_size’)
;

/*****************************************************
— No of trasactions occurring in every 10 minutes
*****************************************************/

select to_char(begin_time,’dd-mon hh24:mi’) begin_time, txncount,
txncount – lag(txncount) over (order by begin_time) txndelta
from v$undostat
order by begin_time;

/*************************************
Find max query length from v$undostat
*************************************/
select * from (
select to_char(begin_time, ‘DD-MON-YY hh24:mi:ss’) BEGIN_TIME ,
round((maxquerylen/60),1) Minutes
from v$undostat
order by maxquerylen desc)
where rownum < 30;

/*******************************************
Shows current undo allocation in Database
*******************************************/
set lines 132
set pages 100
column segment_name format a20
column tablespace_name format a15
column status format a15
column “Total Blocks” for 999999999999
column “Total Space in GB” for 999999999999.99
compute sum of “Extent Count” on report
compute sum of “Total Blocks” on report
compute sum of “Total Space in GB” on report
compute sum of “Total Blocks” on status
compute sum of “Extent Count Blocks” on status
compute sum of “Total Space in GB” on status
break on status skip 1 on status report

select segment_name,
tablespace_name,
count(extent_id) “Extent Count”,
status,
sum(blocks) “Total Blocks”,
round(sum(blocks)*8/(1024*1024),2) “Total Space in GB”
from dba_undo_extents
group by segment_name, tablespace_name, status
order by status;

/********************************************************************************************
— Displays the UNDO statistics which includes the number of ORA-1555s, the longest query time,
— and (Un)Expired Steals (SC). The “steal” values are the number of requests for steals, not
— the actual number of blocks stolen (not reported in this query).
— When the UNExp SC is > 0, that indicates a UNDO space issue.
********************************************************************************************/

select to_char(begin_time, ‘mm/dd/yyyy hh24:mi’) “Int. Start”,
ssolderrcnt “ORA-1555s”, maxquerylen “Max Query”,
unxpstealcnt “UNExp SC”, expstealcnt “Exp SC”,
NOSPACEERRCNT nospace
from v$undostat
order by begin_time
;

–/************************************
— Undo Space used based on v$rollstat
–************************************/
–compute sum of total_used_undo_in_mb on report
–select xacts, round(sum(rssize/1024/1024),2) total_used_undo_in_mb
— from v$rollstat
— group by xacts;

–compute sum of used_undo_size_in_mb on report
–select usn, round(rssize/1024/1024,2) used_undo_size_in_mb, round(hwmsize/1024/1024,2) hwz_size_in_mb, extents, xacts
— from v$rollstat;

/********************************************
Checking Pending UNDO transactions
********************************************/
select usn, UNDOBLOCKSDONE, UNDOBLOCKSTOTAL, pid from v$fast_start_transactions;

Roll Stats 5e Roll20

/************************************
— Undo Space used based on Status
************************************/

compute sum of “Total Space in GB” on report
select tablespace_name, status segment_status, count(extent_id) “Extent Count”, sum(blocks) “Total Blocks”,
sum(blocks)*8/(1024*1024) “Total Space in GB”
from dba_undo_extents
group by tablespace_name, status
;

Roll Statistics

/************************************
— Undo Free Space on DBA_FREE_SPACE
************************************/
select tablespace_name, sum(bytes)/1024/1024/1024 free_space_in_gb
from dba_free_space
where tablespace_name = (select value from v$parameter where name=’undo_tablespace’)
group by tablespace_name
;
spool off