Purge undo tablespace 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

Similar codes

4 votes298 views2 answers
Convert utc to est sql
SQL
0 vote196 views1 answer
Change role postgres
SQL
6 votes2.2K views1 answer
Sql select second max
SQL
3 votes230 views1 answer
How to add unique constraint in mysql table
SQL
-1 vote181 views3 answers
Mysql get age from date
SQL
0 vote204 views1 answer
How to find total working hour in sql
SQL
3 votes365 views6 answers
Sql unique select
SQL
21 votes205 views4 answers
Oracle add column
SQL
40 votes266 views6 answers
Update table mysql
SQL
14 votes195 views3 answers
Alter foreign key
SQL