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

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.