Daily maintenance and viewing of Oracle UNDO tablespace

Common Operations in the undo tablespace:

(1) view the properties of the undo tablespace undotbs1:
SQL> select B. tablespace_name, AUTOEXTENSIBLE, RETENTION from dba_tablespaces a, dba_data_files B
Where a. TABLESPACE_NAME = B. TABLESPACE_NAME and B. TABLESPACE_NAME = 'undotbs1 ';

TABLESPACE_NAME AUT RETENTION
-----------------------------------------
UNDOTBS1 NO NOGUARANTEE

(2) view the usage information of each rollback segment:
Select a. name, B. extents, B. rssize, B. writes, B. xacts, B. wraps
From v $ rollname a, v $ rollstat B where a. usn = B. usn;

(3) determine which users are using the undo segment:
SQL> select a. username, B. name, c. used_ublk from v $ session a, v $ rollname B, v $ transaction c
Where a. saddr = c. ses_addr and B. usn = c. xidusn;

Username name USED_UBLK
-------------------------------------------
NDMC _ SYSSMU1_1255220753 $1
NDMC _ SYSSMU5_1255220754 $1

(4) the amount of UNDO generated per second can be obtained through the following SQL:
SQL> SELECT (SUM (undoblks)/SUM (end_time-begin_time) * 86400) FROM v $ undostat;

(SUM (UNDOBLKS)/SUM (END_TIME-BEGIN_TIME) * 86400)
--------------------------------------------------------------
7.97590055
You can also see the Undo Statistics Section in the AWR report.

(5) Current undo tablespace Usage Status:
SQL> SELECT DISTINCT STATUS, SUM (BYTES), COUNT (*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

Status sum (BYTES) COUNT (*)
-----------------------------------
UNEXPIRED 3225157632 5667
Expired1063518208 1588
ACTIVE 1048576 1

(6) view the active transaction v $ transaction
SQL> select a. SID, A. USERNAME, B. XIDUSN, B. USED_UREC, B. USED_UBLK
From v $ session a, V $ transaction B where a. SADDR = B. SES_ADDR;

Sid username xidusn USED_UREC USED_UBLK
----------------------------------------------------
407 NDMC 15 3 1

SQL> SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks ",
Xidslot as "slot", xidsqn as "seq", status as "txn_status"
From v $ TRANSACTION;

Txn_id undo_seg used_undo_blocks slot seq txn_status
--------------------------------------------------------------------------
14001600733A0C00 20 1 22 801395 ACTIVE