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.<\/p>\n
You can use the following query to check the undo tablespace usage.<\/p>\n
SQL> column tablespace format a20;\r\nSQL> column sum_in_mb format 999999.99;\r\nSQL> 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;\r\n\r\nTABLESPACE\t STATUS\tSUM_IN_MB COUNTS\r\n-------------------- --------- ---------- ----------\r\nUNDOTBS1\t ACTIVE\t 1.00\t 1\r\nUNDOTBS1\t EXPIRED\t 212.44\t 504\r\nUNDOTBS1\t UNEXPIRED\t 120.00\t 361\r\nUNDOTBS2\t EXPIRED\t 123.63\t 343\r\nUNDOTBS2\t UNEXPIRED\t 84.63\t 289<\/pre>\nThe 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.<\/p>\n
SQL> column tablespace format a20;\r\nSQL> column username format a20;\r\nSQL> column status format a10;\r\nSQL> column sum_in_mb format 999999.99;\r\nSQL> select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)\/1024\/1024 sum_in_mb, count(u.segment_name) seg_cnts \r\nfrom dba_undo_extents u left join v$transaction t on u.segment_name = '_SYSSMU' || t.xidusn || '$' \r\nleft join v$session s on t.addr = s.taddr group by u.tablespace_name, s.username, u.status order by 1,2,3\r\n\r\nTABLESPACE\t \t USERNAME\t\t STATUS SUM_IN_MB SEG_CNTS\r\n-------------------- -------------------- ---------- ---------- ----------\r\nUNDOTBS1\t\t\tAHMET\t \t\t\t ACTIVE\t 1.00 \t \t1\r\nUNDOTBS1\t\t\tAHMET\t \t\t \t EXPIRED\t 181.06 467\r\nUNDOTBS1\t\t\tAHMET\t \t\t\t UNEXPIRED\t 172.38 433\r\nUNDOTBS2\t\t\tTESTUSER\t \t\t EXPIRED\t 91.00 301\r\nUNDOTBS2\t\t\tTESTUSER\t \t\t UNEXPIRED\t 144.25 358<\/pre>\nYou 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.<\/p>\n
<\/div>\n