Undo tablespace overview, undo Table Overview

Undo tablespace overview, undo Table Overview
Oracle028undo tablespace Overview


Brief summary of UNDO:

1. the segments in the general tablespace are manually created. The undo tablespace is similar to the general tablespace, but the undo tablespace and the undo tablespace are automatically generated. oracle automatically uses and maintains the undo segments.
2. Generally, the segments in the tablespace are manually used, while those in the undo table are automatically used by oracle.

Show parameter undo_tablespace; // query the current undo tablespace.
NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_tablespace string UNDOTBS1

Select * from v $ rollname; // current number of segments
// The following segment is the rollback segment provided by the system:
1. Only the UNDO segment of the SYSTEM can be used when database objects are operated, that is, the data dictionary is used.
2. If the undo tablespace is broken, the SYSTEM segment may be used.
0 SYSTEM
// The following section exists in UNDOTBS1. The following section is automatically adjusted when the database is running for a period of time.
1 _ syssmuw.378038527 $
2 _ SYSSMU2_2232571081 $
3 _ SYSSMU3_2097677531 $
4 _ SYSSMU4_1152005954 $
5 _ SYSSMU5_1527469038 $
6 _ SYSSMU6_2443381498 $
7 _ SYSSMU7_3286610060 $
8 _ SYSSMU8_2012382730 $
9 _ SYSSMU9_1424341975 $
10 _ SYSSMU10_3550978943 $

Select SEGMENT_NAE, BLOCKS, EXTENTS from dba_segments where SEGMENT_NAME = 'system'; // the size of the middle part of the SYSTEM tablespace

SYSTEM 48 6

-- UNDO tablespace space occupation and data file storage location
Select file_name, bytes/1024/1024 from dba_data_files
Where tablespace_name like '% UNDOTBS % ';

/U01/app/oracle/oradata/jiagulun/undotbs01.dbf 90


Select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS from dba_extents where SEGMENT_NAME = 'system'; // The partition occupied by the segment

SYSTEM 0 1 128 8
SYSTEM 1 1 136 8
SYSTEM 2 1 528 8
SYSTEM Systems 3 1 536 8
SYSTEM 4 1 544 8
SYSTEM 5 1 552 8

Select SEGMENT_NAME, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS from dba_extents where SEGMENT_NAME = '_ SYSSMU7_3286610060 $ ';

_ SYSSMU7_3286610060 $ UNDOTBS1 0 3 224 8
_ SYSSMU7_3286610060 $ UNDOTBS1 1 3 232 8
_ SYSSMU7_3286610060 $ UNDOTBS1 2 3 312 8
_ SYSSMU7_3286610060 $ UNDOTBS1 3 1024 128
_ SYSSMU7_3286610060 $ UNDOTBS1 4 3 11392 128

We can see from the above that the areas in the undo segment of the system are continuous, and the blocks in the areas are also continuous.
However, for undo segments that are not in the system, the blocks are continuous.

Undo tablespace management method: The tablespace is automatically created before 9i, and the segments are automatically created. However, the partitions are manually partitioned, so there will be some problems:
Some relatively large DML language run because the undo segment is enough, there will be problems, insufficient space.
It will be automatically allocated afterwards, and will be automatically reclaimed under some payment requests.
Show parameter undo;
NAME TYPE VALUE
-------------------------------------------------- ----------- Begin -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Undo_tablespace string UNDOTBS1
NAME TYPE VALUE
-------------------------------------------------- ----------- Begin -----------------------------------------------------------------------------------------------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1


Functions of the undo tablespace
1. oracle will use undo when the transaction is started:
When oracle modifies data, oracle puts the modified data into the undo table.
The undo segment of the space. (The more statements are modified, the more undo segments are used .)
Undo can roll back because the modified data is saved
2. Ensure that one session cannot read the uncommitted data of the other session, and store the original data in the undo tablespace. Therefore, the undo ensures read consistency.
3. oracle instance crash recovery: Roll back *

Status of the Undo CIDR Block
Free: Never used
Expired: oracle will be released after undo_retention. In principle, expired will not be released. Time not tested
Inactive: indicates that the transaction has been committed, but it has not been undo_retention. In principle, it is necessary to retain the undo_retention time, but the space pressure is compared.
Large, can be overwritten
Active: indicates the status of the zone in use when the transaction has not been committed. It cannot be overwritten.

Undo_retention
Alter tablespace undotbs1 retention guarantee // set the inactive zone data to be overwritten after at least undo_retention is saved
Alter tablespace undotbs1 retention noguarantee

Select TABLESPACE_NAME, RETENTION from dba_tablespaces;
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 GUARANTEE
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY

When DML is executed, FREE space will be used. When FREE space is insufficient, undo tablespace will be expanded. If it cannot be expanded, EXPIRED will be used. When the tablespace is set to guarantee,
If inactive is not used, the transaction must be suspended. When the tablespace is set to Noguarantee, inactive is used. If inactive is not enough, the transaction is suspended.