Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Undo tablespace is extremely used during peak times. There is a possibility that some operations may be interrupted if you go uncontrolled. You should regularly check the undo tablespace usage.
Check Undo Tablespace Usage
You can use the following query to check the undo tablespace usage.
1 2 3 4 5 6 7 8 9 10 11 | SQL> column tablespace format a20; SQL> column sum_in_mb format 999999.99; SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2; TABLESPACE STATUS SUM_IN_MB COUNTS -------------------- --------- ---------- ---------- UNDOTBS1 ACTIVE 1.00 1 UNDOTBS1 EXPIRED 212.44 504 UNDOTBS1 UNEXPIRED 120.00 361 UNDOTBS2 EXPIRED 123.63 343 UNDOTBS2 UNEXPIRED 84.63 289 |
The information in the status column in the above query also indicates whether the corresponding undo information will be truncated. If its status is ACTIVE, this means that undo is being used in an active process and will not be truncated in any way. If its status is UNEXPIRED, this undo information belongs to a completed transaction, indicating that it has not yet exceeded the time specified by undo_retention. It can be truncated if required. If the status is EXPIRED, this undo information belongs to a completed transaction and indicates that it has exceeded the period specified by undo_retention. This means that this is the first undo information to be truncated. You can use the following query to control user-based undo usage.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> column tablespace format a20; SQL> column username format a20; SQL> column status format a10; SQL> column sum_in_mb format 999999.99; SQL> select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts from dba_undo_extents u left join v$transaction t on u.segment_name = '_SYSSMU' || t.xidusn || '$' left join v$session s on t.addr = s.taddr group by u.tablespace_name, s.username, u.status order by 1,2,3 TABLESPACE USERNAME STATUS SUM_IN_MB SEG_CNTS -------------------- -------------------- ---------- ---------- ---------- UNDOTBS1 AHMET ACTIVE 1.00 1 UNDOTBS1 AHMET EXPIRED 181.06 467 UNDOTBS1 AHMET UNEXPIRED 172.38 433 UNDOTBS2 TESTUSER EXPIRED 91.00 301 UNDOTBS2 TESTUSER UNEXPIRED 144.25 358 |
You can ask users who consume a lot of undo to commit or rollback more frequently in their transactions. In this way, there will be no active use of undo for a long time.
Hi, can yo please update the second script? I just copied it and it’s missing something in the “FROM” segment
Thanks for your comment. I fixed the script.