Status and usage of undo segments and zones in the undo Series

The status and usage of the undo segments and zones of the undo series learn only one thing about the undo tablespace: Save the old values. In AUM, segments are automatically generated, zones are automatically allocated and recycled, and they are not consecutive. Oracle automatically uses the undo segment. In a sense, we only need to pay attention to the size of the undo tablespace. Query which undo tablespace is activated: [SQL] sys @ ORCL> show parameter undo_tablespace www.2cto.com NAME TYPE VALUE =----------- query undo_tablespace string UNDOTBS1 query the number of data files in the undo tablespace: [SQL] sys @ ORCL> col file_name for a72 sys @ ORCL> select file_name, bytes/1024/1024 m from dba_data_files where tablespace_name like '% UNDOTBS %'; FILE_NAME M -------------------- ------------------------------------------------ ----------/U01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8050fkc6 _. dbf 100/u01/app/oracle/oradata/ORCL/datafile/thinkundo. dbf 30 queries the number of online segments in the undo tablespace: [SQL] sys @ ORCL> select * from v $ rollname; www.2cto.com usn name ---------- defaults 0 SYSTEM 1 _ SYSSMU1 $2 _ SYSSMU2 $3 _ SYSSMU3 $4 _ SYSSMU4 $5 _ SYSSMU5 $6 _ SYSSMU6 $7 _ S YSSMU7 $8 _ SYSSMU8 $9 _ SYSSMU9 $10 _ SYSSMU10 $11 rows selected. query the number of partitions and segments: [SQL] sys @ ORCL> select segment_name, extents, blocks from dba_segments where segment_name = '_ SYSSMU3 $ '; SEGMENT_NAME extents blocks ------------- ---------- _ SYSSMU3 $17 256 www.2cto.com: [SQL] sys @ ORCL> select file_id, tablespace_name, segment_name, extent_id, block_id, blocks from dba_extents where 2 segment _ Name = '_ SYSSMU3 $ '; FILE_ID TABLESPACE_NAME SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS ---------- invalid certificate ---------- 2 UNDOTBS1 _ blank $0 41 8 2 UNDOTBS1 _ blank $1 49 8 2 UNDOTBS1 _ blank $2 17 8 2 blank _ blank $3 33 8 2 UNDOTBS1 _ SYSSMU3 $4 161 8 2 UNDOTBS1 _ SYSSMU3 $5 169 8 2 UNDOTBS1 _ SYSSMU3 $6 177 8 2 UNDOTBS1 _ SYSSMU3 $7 185 8 2 UNDOTBS1 _ SYSSMU3 $8 201 8 2 UNDOTBS1 _ SYSSMU3 $9 209 8 2 UNDOTBS1 _ SYSSMU3 $10 217 8 2 UNDOTBS1 _ SYSSMU3 $11 225 8 2 UNDOTBS1 _ SYSSMU3 $12 8 2 UNDOTBS1 _ SYSSMU3 $13 241 8 2 UNDOTBS1 _ SYSSMU3 $14 249 8 2 UNDOTBS1 _ SYSSMU3 $15 257 8 7 UNDOTBS1 _ SYSSMU3 $16 521 128 www.2cto.com 17 rows selected. from this we can also see that the partition allocation in the undo segment is not continuous. Status in the undo segment: 1) free: not allocated to any segment 2) active: transactions in the zone are not committed 3) inactive: transactions in the zone are committed 4) expired: the transaction has been committed and reached undo_retention. Note: We can set undo_retention to keep the inactive zone. If it is not free, it will be automatically extended. If it cannot be extended, expired will be used first. If it is not enough, inactive is used, but if retention is guaranteed by guarantee at this time, inactive cannot be used and a ORA-30036 is reported. [SQL] sys @ ORCL> select extent_id, bytes, status from dba_undo_extents where segment_name = '_ SYSSMU3 $ '; EXTENT_ID bytes status ---------- -------- --------- 0 65536 EXPIRED 1 65536 EXPIRED 2 65536 EXPIRED 3 65536 EXPIRED 4 65536 EXPIRED 5 65536 EXPIRED 6 65536 EXPIRED 7 65536 EXPIRED 8 65536 EXPIRED 9 65536 EXPIRED 10 65536 limit 11 65536 EXPIRED 12 65536 EXPIRED 13 65536 EXPIRED 14 65536 EXPIRED 15 65536 EXPIRED 16 1048576 EXPIRED www.2cto.com 17 rows selected. author: linwaterbin