Oracle undotbs usage

-- UNDO tablespace current usage / available space
SELECT a.TABLESPACE_NAME, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) AS FREEMB
FROM (SELECT round(sum(BYTES) / 1e6) AS SIZEMB, b.TABLESPACE_NAME
      FROM DBA_DATA_FILES a, DBA_TABLESPACES b
      WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME AND b.CONTENTS LIKE 'UNDO'
      GROUP BY b.TABLESPACE_NAME) a,
     (SELECT c.TABLESPACE_NAME, sum(BYTES) / 1e6 AS USAGEMB
      FROM DBA_UNDO_EXTENTS c
      WHERE STATUS <> 'EXPIRED'
      GROUP BY c.TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME;

Source: smarttechways.com

Oracle undotbs usage

-- UNDOTBS usage per User:
SELECT u.TABLESPACE_NAME          AS TABLESPACE,
       s.USERNAME,
       u.STATUS,
       sum(u.BYTES) / 1024 / 1024 AS SUM_IN_MB,
       count(u.SEGMENT_NAME)      AS SEG_CNTS
FROM DBA_UNDO_EXTENTS u, V$TRANSACTION T, V$SESSION s
WHERE T.ADDR = s.TADDR
GROUP BY u.TABLESPACE_NAME, s.USERNAME, u.STATUS
ORDER BY 1, 2, 3;

Similar codes

1 vote286 views1 answer
Sql auto timestamp
SQL
0 vote337 views1 answer
Excel export from sql using python
SQL
0 vote325 views1 answer
Sql select lowest value row
SQL
15 votes333 views4 answers
Mysql export database command line
SQL
0 vote309 views1 answer
Get foreign table names mysql
SQL
11 votes338 views3 answers
How to get last row of table in sql
SQL
16 votes321 views4 answers
Find duplicates mysql
SQL
1 vote343 views3 answers
How to pass password to mysql command line
SQL
0 vote302 views1 answer
Mysql workbench download for iinux mint 19.3
SQL
0 vote326 views1 answer
Mysql query to select the highest value
SQL