Cacher is the code snippet organizer for pro developers

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION
  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';

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  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'
/

set lines 1000
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
SELECT BEGIN_TIME,END_TIME,MAXQUERYID,UNDOBLKS,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS FROM V$UNDOSTAT ORDER BY 1 ASC;

select BEGIN_TIME,END_TIME,ACTIVEBLKS,EXPIREDBLKS,UNEXPIREDBLKS from V$UNDOSTAT;

SELECT UNDOTSN,UNDOBLKS,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS FROM V$UNDOSTAT;

SELECT DISTINCT STATUS, SUM(BYTES/1024/1024), COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name='UNDOTBS1' GROUP BY STATUS;

SELECT DISTINCT STATUS, SUM(BYTES/1024/1024), COUNT(*) FROM DBA_UNDO_EXTENTS where tablespace_name='UNDOTBS2' GROUP BY STATUS;

select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks
from v$session a, v$transaction b
where a.saddr=b.ses_addr ;

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents group by status order by status;

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

col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999

select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like '%UND%' ) allocated_MB,
( select sum(bytes)/1024/1024/1024 from dba_free_space
where tablespace_name like '%UND%') free_GB,
( select sum(bytes)/1024/1024/1024 from dba_undo_extents
where tablespace_name like '%UND%') Used_GB
from dual
/

select tablespace_name , sum(blocks)*8/(1024)  SPACE_IN_USE_ACTIVE_UNEXPIRED from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by  tablespace_name;

select tablespace_name , sum(blocks)*8/(1024)  SPACE_IN_USE_ACTIVE from dba_undo_extents where status IN ('ACTIVE') group by  tablespace_name;

select tablespace_name , sum(blocks)*8/(1024)  SPACE_IN_USE_UNEXPIRED from dba_undo_extents where status IN ('UNEXPIRED') group by  tablespace_name;

col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999

select
( select sum(bytes)/1024/1024/1024 from dba_data_files
where tablespace_name like '%UND%' ) as TBS_allocated_GB,
( select sum(bytes)/1024/1024/1024 from dba_free_space
where tablespace_name like '%UND%') as TBS_free_GB,
( select sum(maxbytes)/1024/1024/1024 from dba_data_files
where tablespace_name like '%UND%' ) as TBS_maxsize_GB,
( select sum(bytes)/1024/1024/1024 from dba_undo_extents
where tablespace_name like '%UND%' AND status IN ('EXPIRED')) as Reusable_GB_EXPIRED,
( select sum(bytes)/1024/1024/1024 from dba_undo_extents
where tablespace_name like '%UND%' AND status IN ('ACTIVE','UNEXPIRED')) as Used_GB_Active_UNEXPIRED
from dual
/