2 Data Blocks, Extents, and Segments


001 This chapter describes the nature of and relationships among the logical storage structures in the Oracle database server.
 
本章讲述Oracle数据库中各种逻辑存储结构(logical storage structure)的特性和相互关系。
002 This chapter contains the following topics: 本章包含以下主题:
003

Introduction to Data Blocks, Extents, and Segments

2.1 数据块,数据扩展,段简介

004 Oracle allocates logical database space for all data in a database. The units of database space allocation are data blocks, extents, and segments. Figure 2-1 shows the relationships among these data structures:
 
Oracle数据库负责为所有数据分配逻辑存储空间。数据库空间的分配单位有数据块(Data Block),数据扩展(Extent),和段(Segment)。图2-1 说明了这些数据结构之间的关系。
005 Figure 2-1 The Relationships Among Segments, Extents, and Data Blocks
 
图2-1 段,数据扩展,数据块之间的关系
006

Description of Figure 2-1 follows
 

007 At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.
 
Oracle存储数据的最小粒度(finest level of granularity)被称为数据块(data block)(也叫做逻辑块(logical block),Oracle块(Oracle block)或(page))。一个数据块对应于磁盘上数个字节(byte)的物理数据库空间。
008 The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks allocated for storing a specific type of information.
 
处于数据块之上的逻辑数据库空间是数据扩展(extent)。数据扩展是为存储数据而分配的一组连续的数据块。
009 The level of logical database storage greater than an extent is called a segment. A segment is a set of extents, each of which has been allocated for a specific data structure and all of which are stored in the same tablespace. For example, each table's data is stored in its own data segment, while each index's data is stored in its own index segment. If the table or index is partitioned, each partition is stored in its own segment.
 
位于数据扩展之上的逻辑数据库存储结构是(segment)。段由一组数据扩展 (extent)构成,这些数据扩展位于同一表空间(tablespace)中,用于存储各种逻辑数据结构。例如每个表(table)的数据都存储在其自身的数据段(data segment)中,每个索引(index)的数据都存储在其自身的索引段(index segment)中。如果表或索引是分区存储(partitioned)的,则每个分区拥有自己的段。
010 Oracle allocates space for segments in units of one extent. When the existing extents of a segment are full, Oracle allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.
 
Oracle为段(segment)分配空间时以数据扩展(extent)为单位。当段内已有的数据扩展没有可用空间时,Oracle为此段分配一个新的数据扩展。因为数据扩展是随需分配的,所以一个段内的数据扩展在磁盘上未必是连续的。
011 A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents from more than one file; that is, the segment can span datafiles. However, each extent can contain data from only one datafile.
 
一个段(segment)以及属于她的所有数据扩展(extent)必须包含在同一表空间(tablespace)中。但在一个表空间内,属于同一个段的数据扩展可以分布 在多个数据文件(datafile)上,即段可以跨文件存储。但是每个数据扩展只能包含于同一个数据文件中。
012 Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.
 
在用户分配一个新的数据扩展(extent)时,其中的数据块(data block)未必被同时分配。如果用户是为某个数据库对象分配数据扩展 ,那么数据块也同时被立即分配并加入可用块列表(free list)中。如果数据扩展并非专为某数据库对象分配,那么数据块只在高水位线(high water mark)移动时才被分配。高水位线是段(segment)中已用和未用空间的边界。
013

Note:

Oracle recommends that you manage free space automatically. See "Free Space Management".

提示:

Oracle建议用户使用可用空间自动管理功能。详见“可用空间管理”。

014

Overview of Data Blocks

2.2 数据块概述

015 Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.
 
Oracle对数据库数据文件(datafile)中的存储空间进行管理的单位是数据块(data block)。数据块是数据库中最小的(逻辑)数据单位。与数据块对应的,所有数据在操作系统级的最小物理存储单位是字节(byte)。每种操作系统都有一个被称为块容量(block size)的参数。Oracle每次获取数据时,总是访问整数个(Oracle)数据块,而不是按照操作系统块的容量访问数据。
016 The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.
 
数据库中标准的数据块(data block)容量是由初始化参数 DB_BLOCK_SIZE 指定的。除此之外,用户还可以指定五个非标准的数据块容量(nonstandard block size)。数据块容量应该设为操作系统块容量的整数倍(同时小于数据块容量的最大限制),以便减少不必要的I/O操作。Oracle数据块是Oracle可以使用和分配的最小存储单位。
017

See Also:

  • Your Oracle operating system-specific documentation for more information about data block sizes
  • Multiple Block Sizes

另见:

018

Data Block Format

2.2.1 数据块结构

019 The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2 illustrates the format of a data block.
 
在Oracle中,不论数据块中存储的是表(table)、索引(index)或簇表(clustered data),其内部结构都是类似的。图2-2 说明了数据块的结构。
020 Figure 2-2 Data Block Format
 
图2-2 数据块结构
021

Description of Figure 2-2 follows
 

022

This image shows the parts of a database block: common and variable header, table directory, row directory, free space, and row data. The parts are described in subsequent sections. Arrows indicate that the amount of free space in the database block is variable.

本图显示了数据块的各个组成部分,包括:数据块头(包括标准内容和可变内容)(common and variable header),表目录区(table directory),行目录区(row directory),可用空间区(free space),行数据区(row data)。以下各节将分别讲解各个组成部分。图中两个箭头表示一个数据块中的可用空间区的容量是可变的。

023

Header (Common and Variable)

2.2.1.1 数据块头(包括标准内容和可变内容)

024 The header contains general block information, such as the block address and the type of segment (for example, data or index).
 
数据块头(header)中包含了此数据块的概要信息,例如块地址(block address)及此数据块所属的段(segment)的类型(例如,表或索引)。
025

Table Directory

2.2.1.2 表目录区

026 This portion of the data block contains information about the table having rows in this block.
 
如果一个数据表在此数据块中储存了数据行,那么数据表的信息将被记录在数据块的表目录区(table directory)中。
027

Row Directory

2.2.1.3 行目录区

028 This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).
 
此区域包含数据块中存储的数据行的信息(每个数据行片断(row piece) 在行数据区(row data area)中的地址)。[一个数据块中可能保存一个完整的数据行,也可能只保存数据行的一部分 ,所以文中使用row piece]
 
029 After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
 
当一个数据块(data block)的行目录区(row directory)空间被使用后,即使数据行被删除(delete),行目录区空间也不会被回收。举例来说,当一个曾经包含50条记录的数据块被清空后,其块头(header)的行目录区仍然占用100字节(byte)的空间。只有在数据块中插入(insert)新数据时,行目录区空间才会被 重新利用。
030

Overhead

2.2.1.4 管理开销

031 The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.
 
数据块头(data block header),表目录区(table directory),行目录区(row directory)被统称为管理开销(overhead)。其中 有些开销的容量是固定的;而有些开销的总容量是可变的。数据块中固定及可变管理开销的容量平均在84到107字节(byte)之间。
032

Row Data

2.2.1.5 行数据

033 This portion of the data block contains table or index data. Rows can span blocks.
 
数据块(data block)中行数据区(row data)包含了表或索引的实际数据。一个数据行可以跨多个数据块。
 
034
See Also:

"Row Chaining and Migrating"
另见:

行链接(Row Chaining)及行迁移(Row Migrating)
035

Free Space

2.2.1.6 可用空间区

036 Free space is allocated for insertion of new rows and for updates to rows that require additional space (for example, when a trailing null is updated to a nonnull value).
 
在插入新数据行,或在更新数据行需要更多空间时(例如,原来某行最后一个字段为空(trailing null),现在要更新为非空值),将 使用可用空间区(free space)中的空间。
037 In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block. The space required for transaction entries is operating system dependent; however, transaction entries in most operating systems require approximately 23 bytes.
 
如果一个数据块(data block)属于表或簇表的数据段(data segment),或属于索引的索引段(index segment),那么在其可用空间区中还可能会存储事务条目(transaction entry)。如果一个数据块中的数据行(row)正在由 INSERTUPDATEDELETE,及 SELECT...FOR UPDATE 语句访问,此数据块中就需要保存事务条目。事务条目所需的存储空间依据操作系统而定。在常见的操作系统中事务条目大约需要占用23字节(byte)。
038

Free Space Management

2.2.2 可用空间管理

039 Free space can be managed automatically or manually.
 
可用空间可以被手动或自动管理。[注意本节中的“可用空间”与“可用空间区”的区别]
 
040 Free space can be managed automatically inside database segments. The in-segment free/used space is tracked using bitmaps, as opposed to free lists. Automatic segment-space management offers the following benefits:
  • Ease of use
  • Better space utilization, especially for the objects with highly varying row sizes
  • Better run-time adjustment to variations in concurrent access
  • Better multi-instance behavior in terms of performance/space utilization
数据库中,每个段(segment)的可用空间可以被自动管理。段内的可用/已用空间以位图(bitmap)形式记录,这与可用块以列表方式的管理不同。段空间自动管理(Automatic segment-space management)具备以下优势:
  • 易于使用
  • 空间利用效率更高,尤其针对每行数据容量差异大的表(或其他对象)
  • 能够更好地针对当前数据的情况实时调整
  • Better multi-instance behavior in terms of performance/space utilization
041 You specify automatic segment-space management when you create a locally managed tablespace. The specification then applies to all segments subsequently created in this tablespace.
 
用户可以在创建一个本地管理的表空间(locally managed tablespace)时选择自动段空间管理(automatic segment-space management)功能。这样在此表空间内创建的段都将默认地设置为自动段空间管理。
042

See Also:

Oracle Database Administrator's Guide

另见:

Oracle 数据库管理员指南

043

Availability and Optimization of Free Space in a Data Block

2.2.2.1 数据块可用空间的有效性及优化

044 Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:
  • If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available.
  • If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.
有两种SQL语句可以增加数据块中的可用空间:分别是 DELETE 语句,和将现有数据值更新为占用容量更小值的 UPDATE 语句。在以下两种条件下,上述两中操作释放的空间可以被后续的 INSERT 语句使用:
  • 如果 INSERT 语句与上述两种操作在同一事务(transaction)中,且位于释放空间的语句之后,那么 INSERT 语句可以使用被释放的空间。
  • 如果 INSERT 语句与释放空间的语句在不同的事务中(比如两者是由不同的用户提交的),那么只有在释放空间的语句提交后,且插入数据必需使用此数据块时,INSERT 语句才会使用被释放的空间。
045 Released space may or may not be contiguous with the main area of free space in a data block. Oracle coalesces the free space of a data block only when (1) an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece, and (2) the free space is fragmented so the row piece cannot be inserted in a contiguous section of the block. Oracle does this compression only in such situations, because otherwise the performance of a database system decreases due to the continuous compression of the free space in data blocks.
 
数据块(data block)中被释放出的空间未必与可用空间区(free space)相连续。Oracle在满足以下条件时才会将释放的空间合并到可用空间区:(1)INSERTUPDATE 语句选中了一个有足够可用空间容纳新数据的数据块,(2)但是此块中的可用空间不连续,数据无法被写入到数据块中连续的空间里。Oracle只在 满足上述条件时才对数据块中的可用空间进行合并,这样做是为了避免过于频繁的空间合并工作影响数据库性能。
046

Row Chaining and Migrating

2.2.2.2 行链接(Row Chaining)及行迁移(Row Migrating)

047 In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.
 
有两种情况会导致表中某行数据过大,一个数据块(data block)无法容纳。第一种情况,当一行数据被插入时一个数据块就无法容纳。在这种情况下Oracle将这行数据存储在段内的一个数据块(chain)中。在插入数据量大的行时常会发生行链接(row chaining),例如一个包含数据类型为 LONGLONG RAW 列的数据行。此时行链接不可避免。
048 However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.
 
第二种情况,原本存储在一个数据块(data block)内的数据行,因为更新操作导致长度增长,而所在数据块的可用空间也不能容纳增长后的数据行。在这种情况下,Oracle将此行数据迁移(migrate)到新的数据块中。Oracle在被迁移数据行原来所在位置保存一个指向新数据块的指针。被迁移数据行的 rowid 保持不变。
049 When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.
 
当数据行发生链接(chain)或迁移(migrate)时,对其访问将会造成 I/O 性能降低,因为Oracle为获取这些数据行的数据时,必须访问更多的数据块(data block)。
050

See Also:

另见:

051

PCTFREE, PCTUSED, and Row Chaining

2.2.3 PCTFREE,PCTUSED,及行链接(Row Chaining)

052 For manually managed tablespaces, two space management parameters, PCTFREE and PCTUSED, enable you to control the use of free space for inserts and updates to the rows in all the data blocks of a particular segment. Specify these parameters when you create or alter a table or cluster (which has its own data segment). You can also specify the storage parameter PCTFREE when creating or altering an index (which has its own index segment).
 
在手动管理的表空间(manually managed tablespaces)中,用户可以使用 PCTFREEPCTUSED 这两个存储管理参数来控制对某段(segment)进行插入和更新操作时,如何利用属于此段的数据块(data block)中的可用空间。用户也可以在创建或修改索引时为其设定 PCTFREE 参数(索引存储在索引段(index segment)中)。
053

Note:

This discussion does not apply to LOB datatypes (BLOB, CLOB, NCLOB, and BFILE). They do not use the PCTFREE storage parameter or free lists.

See "Overview of LOB Datatypes" for information.

提示:

本节的内容并不适用于 LOB 数据类型(BLOBCLOBNCLOB,及 BFILE)。 这些类型的数据存储时不使用 PCTFREE 参数及可用块列表(free list)。

详见“LOB 数据类型概述”。
054

The PCTFREE Parameter

2.2.3.1 PCTFREE 参数

055 The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
 
PCTFREE 参数用来设置一个数据块(data block)中至少需要保留(reserve)多少可用空间(百分比值),为数据块中已有数据更新时可能发生的数据量增长做准备。例如,当用户用 CREATE TABLE 语句创建表时指定了以下参数:
056

PCTFREE 20

PCTFREE 20
057 This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size. Figure 2-3  illustrates PCTFREE.
 
这个参数设定了此表对应的数据段(data segment)中的每个数据块(data block)至少保留20%的可用空间,以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%,用户就可以向其中插入新数据,数据行被放入行数据区(row data area),相关信息被写入数据块头(overhead area)。图 2-3 说明了 PCTFREE 的作用。
058 Figure 2-3 PCTFREE
 
图 2-3 PCTFREE
059

Description of Figure 2-3 follows
 

060

The PCTUSED Parameter

2.2.3.2 PCTUSED 参数

061 The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a CREATE TABLE statement:
 
PCTUSED 参数用于决定一个数据块(data block)是否可被用于插入新数据,她的依据是数据区(row data)与数据块头(overhead)的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于 PCTFREE 参数的规定时,Oracle就认为此数据块无法被用于插入新数据,直到数据块中的占用容量比例小于 PCTUSED 参数的限定。在占用容量比例大于 PCTUSED 参数的限定之前,Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如,当用户用 CREATE TABLE 语句创建表时指定了以下参数:
062

PCTUSED 40

PCTUSED 40
063 In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 2-4 illustrates this.
 
在例子中,当此表的某数据块占用容量比例高于40%时,Oracle不会将此数据块用于插入新数据行(假设此数据块的可用空间曾经低于 PCTFREE 的限定)。图 2-4 说明了 PCTUSED 的作用。
064 Figure 2-4 PCTUSED
 
图 2-4 PCTUSED
065

Description of Figure 2-4 follows
 

066

How PCTFREE and PCTUSED Work Together

2.2.3.3 PCTFREE 和 PCTUSED 如何协同发挥作用

067 PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 2-5 illustrates the interaction of these two parameters.
 
PCTFREEPCTUSED 共同作用可以优化数据块(data block)的空间使用。图 2-5 说明了这两种参数的交互作用。
068 Figure 2-5 Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED
 
2-5 使用 PCTFREE 和 PCTUSED 参数管理数据块的可用空间
069

Description of Figure 2-5 follows
 

070

This image shows how PCTFREE and PCTUSED work together to maintain free space in a data block.

In step 1, rows are inserted up to 80% only, because PCTFREE specifies that 20% of the block must remain open for updates of existing rows.

In step 2, updates to existing rows use the free space reserved in the block. No new rows can be inserted into the block until the amount of used space is 39% or less.

In step 3, after the amount of used space falls below 40%, new rows can again be inserted into this block.

In step 4, rows are inserted up to 80% only, because PCTFREE specifies that 20% of the block must remain open for updates of existing rows. This cycle continues.

上图说明了 PCTFREEPCTUSED 如何共同作用以管理数据块(data block)可用空间的使用。

在第一步中,数据块占用空间比例小于80%时才能插入新数据,因为 PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。

在第二步中,对数据块中已有数据的更新操作可以使用数据块中的保留空间。只有当数据块内的占用空间比例低于40%时才能向其中插入新数据。

在第三步中,当数据块内的占用空间比例低于40%时,此数据块再次可以被用于插入新数据。

在第四步中,数据块占用空间比例小于80%时才能插入新数据,因为 PCTFREE 参数限定必须保留20%的可用空间用于块内已有数据的更新。此过程如此往复循环。
071 In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space (PCTFREE). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than PCTFREE, the space reserved for updates but not accessible to inserts.
 
在新分配的数据块中(data block),可用于插入(insert)数据的空间等于数据块总容量减去数据块头(block overhead)再减去预留可用空间(PCTFREE)。而更新(update)数据块内已有数据可使用数据块中的所有可用空间。因此,更新操作能够使数据块内的可用空间低于的 PCTFREE 限制,因为这些空间是专为更新操作而预留的。
072 For each data and index segment, Oracle maintains one or more free lists—lists of data blocks that have been allocated for that segment's extents and have free space greater than PCTFREE. These blocks are available for inserts. When you issue an INSERT statement, Oracle checks a free list of the table for the first available data block and uses it if possible. If the free space in that block is not large enough to accommodate the INSERT statement, and the block is at least PCTUSED, then Oracle takes the block off the free list. Multiple free lists for each segment can reduce contention for free lists when concurrent inserts take place.
 
在每个数据段(data segment)与索引段(index segment)中,Oracle管理着一个或多个可用块列表(free list)--其中列出了所有属于此段的数据扩展(extent),且可用空间比例大于 PCTFREE 限定的数据块。这些块可以被插入(insert)操作使用。当用户提交了 INSERT 语句后,Oracle从可用块列表中选择第一个有效的数据块使用。如果此数据块的可用空间不够容纳 INSERT 语句提交的数据,且此块的占用容量已经超过PCTUSED 的限定,Oracle就将其从可用块列表中移出。一个段可以同时使用多个可用块列表,以减少对一个表进行并发插入(concurrent insert)时产生的竞争。
073 After you issue a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions.
 
当用户提交了 DELETEUPDATE 语句后,Oracle处理语句并检查相关数据块中的占用空间比例是否小于 PCTUSED 的规定。如果满足,那么这个数据块就被放入当前事务(transaction)正在使用的可用块列表(free list)的头部,如果当前事务还需要写入数据,此块将被首先使用。当事务提交后,此数据块中的可用空间还可被其他事务使用。
074

Overview of Extents

2.3 数据扩展概述

075 An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.
 
数据扩展(extent)是由一组连续的数据块(data block)构成的数据库逻辑存储分配单位。而段(segment)则是由一个或多个数据扩展构成。当一个段中已有空间已经用完,Oracle为这个段分配新的数据扩展。
076

When Extents Are Allocated

2.3.1 数据扩展何时被分配

077 When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows.
 
当用户创建数据表时,Oracle为此表的数据段(data segment)分配一个包含若干数据块(data block)的初始数据扩展(initial extent)。虽然此时数据表中还没有数据,但是在此初始数据扩展中的数据块已经为插入新数据做好了准备。
078 If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.
 
如果一个段(segment)的初始数据扩展(initial extent)中的数据块(data block)都已装满,且有新数据插入需要空间时,Oracle自动为这个段分配一个增量数据扩展(incremental extent)。增量数据扩展是一个段中继已有数据扩展之后分配的后续数据扩展,她的容量大于或等于之前的数据扩展。
079 For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.
 
为了管理的需要,每个段(segment)的段头(header block)中包含一个记录此段所有数据扩展(extent)的目录。
080
Note:

This chapter applies to serial operations, in which one server process parses and runs a SQL statement. Extents are allocated somewhat differently in parallel SQL statements, which entail multiple server processes.
提示:

本章的内容适用于串行执行(serial operation)的情况,即只有一个服务进程解析、执行 SQL 语句。对于并行执行(parallel)的 SQL 语句(即一个语句由多个服务进程执行),数据扩展(extent)的分配情况有所不同。
081

Determine the Number and Size of Extents

2.3.2 如何决定数据扩展分配时的数量与容量

082 Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments. They control how Oracle allocates free database space for a given segment. For example, you can determine how much space is initially reserved for a table's data segment or you can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of the CREATE TABLE statement. If you do not specify a table's storage parameters, then it uses the default storage parameters of the tablespace.
 
每个段(segment)的定义中都包含了数据扩展(extent)的存储参数(storage parameter)。存储参数适用于各种类型的段。这个参数控制着Oracle如何为段分配可用空间。例如,用户可以在 CREATE TABLE 语句中使用 STORAGE 子句设定存储参数,决定创建表时为其数据段(data segment)分配多少初始空间,或限定一个表最多可以包含多少数据扩展。如果用户没有为表设定存储参数,那么表在创建时使用所在表空间(tablespace)的默认存储参数。
083 You can have dictionary managed tablespaces, which rely on data dictionary tables to track space utilization, or locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. Because of the better performance and easier manageability of locally managed tablespaces, the default for non-SYSTEM permanent tablespaces is locally managed whenever the type of extent management is not explicitly specified.
 
用户既可以使用数据字典管理的表空间(dictionary managed tablespace)(依赖数据字典表监控空间的利用情况),也可以使用本地管理的表空间(locally managed tablespace)(使用位图(bitmap)来标记可用与已用空间)。由于本地管理的表空间性能较好且易于管理,当用户没有显式地设定数据扩展(extent)管理参数时,除了 SYSTEM 之外的所有永久表空间(permanent tablespace)默认使用本地管理方式。
084 A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.
  • For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.
  • For system-managed extents, Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with 'segment space management auto', and if the database block size is 16K or higher, then Oracle manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.
在一个本地管理的表空间中,其中所分配的数据扩展(extent)的容量既可以是用户设定的固定值,也可以是由系统自动决定的可变值。当用户创建表空间(tablespace)时可以使用 UNIFORM (用户指定)或 AUTOALLOCATE (由系统管理)子句设定数据扩展的分配方式。
  • 对于固定容量(UNIFORM)的数据扩展,用户可以为数据扩展设定容量或使用默认大小(1 MB)。用户须确保每个数据扩展的容量至少能包含5个数据块(database block)。本地管理(locally managed)的临时表空间(temporary tablespace)在分配数据扩展时只能使用此种方式。
  • 对于由系统管理(AUTOALLOCATE)的数据扩展,由Oracle决定新增数据扩展的最佳容量,其最小容量为 64 KB。如果创建表空间时使用了“segment space management auto”子句,且数据块容量大于等于 16 KB,Oracle扩展一个段时(segment)所创建的数据扩展的最小容量为 1 MB。对于永久表空间(permanent tablespace)上述参数均为默认值。
085 The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be specified at the tablespace level for locally managed tablespaces. They can, however, be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and MINEXTENTS are used together to compute the initial size of the segment. After the segment size is computed, internal algorithms determine the size of each extent.
 
在本地管理的表空间(locally managed tablespace)中,INITIALNEXTPCTINCREASE,和 MINEXTENTS 这四个存储参数可以作用于段(segment),但不能作用于表空间。INITIALNEXTPCTINCREASE,和 MINEXTENTS 相结合可以用于计算段的初始容量。当段容量确定后,Oracle使用内部算法确定其中每个初始数据扩展(extent)的容量。
086

See Also:

另见:

087

How Extents Are Allocated

2.3.3 数据扩展如何被分配

088 Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.
 
Oracle依据表空间管理方式的不同(本地管理(locally managed)或数据字典管理(dictionary managed)),选择不同的算法分配数据扩展(extent)。
089 With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.
 
对于本地管理的表空间(locally managed tablespace),Oracle在为新的数据扩展(extent)寻找可用空间时,首先选择一个属于此表空间的数据文件(datafile),再搜索此数据文件的位图(bitmap)查找连续的数据块(free block)。如果此数据块中没有足够的连续可用空间,Oracle将查询其他数据文件。
090

Note:

Oracle strongly recommends that you use locally managed tablespaces.

提示:

Oracle 强烈建议用户使用本地管理的表空间(locally managed tablespace)。
091

When Extents Are Deallocated

2.3.4 数据扩展如何被回收

092 The Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object.
 
Oracle 数据库提供了 Segment Advisor 工具,她依据方案对象(schema object)存储空间中的碎片程度来判断此对象中是否包含可回收的空间。
093

See Also:

另见:

094 In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement). Exceptions to this include the following:
  • The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
  • A database administrator (DBA) can deallocate unused extents using the following SQL syntax:

    ALTER TABLE table_name DEALLOCATE UNUSED;
  • Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
一般来说,在用户将一个段(segment)对应的方案对象(schema object)移除(使用 DROP TABLEDROP CLUSTER 语句)之前,此段的数据扩展(extent)不会被回收到表空间(tablespace)中,但是以下情况例外:
  • 表,簇表的所有者(owner)或拥有 DELETE ANY 权限的用户, 可以使用 TRUNCATE...DROP STORAGE 语句将表,簇表的数据清除
  • DBA 可以使用以下语法收回一个段中未使用的数据扩展:

    ALTER TABLE table_name DEALLOCATE UNUSED;
  • 如果用户为回滚段(rollback segment)设定了 OPTIMAL 参数,Oracle将周期性地从其中回收数据扩展。
095 When extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.
 
当数据扩展(extent)被释放后,Oracle修改数据文件(datafile)中的位图(bitmap)(对于本地管理的表空间)或更新数据字典(对于数据字典管理的表空间),将回收的数据扩展视为可用空间。被释放的数据扩展中的数据无法继续访问。
096

See Also:

另见:

097

Extents in Nonclustered Tables

2.3.4.1 非簇表中的数据扩展

098 As long as a nonclustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table. Oracle inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Oracle does not reclaim the data blocks for use by other objects in the tablespace.
 
只要用户没有移除(drop)或清空(truncate)非簇表(nonclustered table),那么对应数据段(data segment)内的任何数据块(data block)都不会被回收。Oracle会在插入数据时使用这些数据块(只要数据块有足够的空间)。即使用户删除(delete)了表的所有行,Oracle也不会回收此表的数据块供表空间内的其他方案对象(schema object)使用。
099 After you drop a nonclustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents of the table's data and index segments for the tablespaces that they were in and makes the extents available for other schema objects in the same tablespace.
 
当用户移除(drop)了一个非簇表(nonclustered table),Oracle将在需要空间时回收此表对应的数据扩展(extent)。Oracle将此表对应的数据段(data segment)、索引段(index segment)中包含的数据扩展(extent)全部收回,并供存储于此表空间(tablespace)的其他方案对象(chema object)使用。
100 In dictionary managed tablespaces, when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. This is called coalescing extents. Coalescing extents is not necessary in locally managed tablespaces, because all contiguous free space is available for allocation to a new extent regardless of whether it was reclaimed from one or more extents.
 
对于数据字典管理的表空间(dictionary managed tablespace),当一个段(segment)需要的数据扩展(extent)比现有可用扩展的都大时,Oracle通过寻找、合并连续的被回收的数据扩展来形成更大的数据扩展。这个过程被称为数据扩展合并(coalescing)。本地管理的表空间(locally managed tablespace)没有必要进行数据扩展合并,因为所有连续空间都可以被用于分配新数据扩展,而无需关心所用空间是如何被回收的。
101

Extents in Clustered Tables

2.3.4.2 簇表中的数据扩展

102 Clustered tables store information in the data segment created for the cluster. Therefore, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are deallocated. You can also truncate clusters (except for hash clusters) to free extents.
 
簇表(clustered table)的数据存储在为整个簇(cluster)分配的数据段(data segment)中。因此当用户移除(drop)簇中的一个表时,数据段还要为簇中的其他表保留,因此没有数据扩展(extent)被回收。用户可以通过清空(truncate)整个簇来回收属于簇的数据扩展(哈希簇(hash cluster)除外)。
103

Extents in Materialized Views and Their Logs

2.3.4.3 物化视图及其日志的数据扩展

104 Oracle deallocates the extents of materialized views and materialized view logs in the same manner as for tables and clusters.
 
Oracle回收属于物化视图(materialized view)及其日志(materialized view log)的数据扩展的方式与表或簇相同。
105

See Also:

"Overview of Materialized Views"

另见:

物化视图概述
106

Extents in Indexes

2.3.4.4 索引的数据扩展

107 All extents allocated to an index segment remain allocated as long as the index exists. When you drop the index or associated table or cluster, Oracle reclaims the extents for other uses within the tablespace.
 
只要索引存在,那么对应索引段(index segment)的所有数据扩展(extent)都不会被回收。当用户移除(drop)索引或相关的表、簇表时,Oracle才将索引使用的数据扩展回收。
108

Extents in Temporary Segments

2.3.4.5 临时段(temporary segment)的数据扩展

109 When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment in a temporary tablespace of the user issuing the statement and then returns the extents to the tablespaces.
 
当Oracle执行完一个 SQL 语句,且在执行过程中使用的临时段(temporary segment)时,Oracle自动地移除(drop)临时段,并将属于此临时段的数据扩展(extent)空间还给临时段所在的表空间(tablespace)。当用户执行单一排序(single sort)语句时,Oracle将在此用户的临时表空间(temporary tablespace)中创建为这个排序服务的临时段(temporary segment),并在执行后将此临时段使用的数据扩展回收。
110 Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other multiple sorts.
 
但是对于多重排序(multiple sort),可能会使用临时表空间(temporary tablespace)中专为排序而创建的排序段(sort segment)。在每个实例中只会分配一个排序段,在排序操作使用后也不会被回收,而是继续供以后的多重排序使用。
111 A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.
 
同一会话或同一事务的多个 SQL 语句,有可能共同使用一个临时表(temporary table)的临时段(temporary segment)存储临时数据。在这种情况下,Oracle在会话或事务结束后 才移除(drop)临时段,并为临时段所在表空间回收使用的数据扩展(extent)。
112

See Also:

另见:

113

Extents in Rollback Segments

2.3.4.6 回滚段(rollback segment)的数据扩展

114 Oracle periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is, it has too many extents), then Oracle automatically deallocates one or more extents from the rollback segment.
 
Oracle周期性地检查数据库回滚段(rollback segment)的容量是否超过了最优值(optimal size)。如果回滚段超过了这个最优值(即回滚段中的数据扩展(extent)过多),Oracle将自动地从回滚段回收一些数据扩展。
115

Overview of Segments

2.4 段概述

116 A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment, and for each index, Oracle allocates one or more extents to form its index segment.
 
段(segment)由一组数据扩展(extent)构成,其中存储了表空间(tablespace)内各种逻辑存储结构的数据。例如,Oracle能为每个表的数据段(data segment)分配数据扩展,还能为每个索引的索引段(index segment)分配数据扩展。
117 This section contains the following topics: 本节包含以下主题:
118

Introduction to Data Segments

2.4.1 数据段简介

119 A single data segment in an Oracle database holds all of the data for one of the following:
  • A table that is not partitioned or clustered
  • A partition of a partitioned table
  • A cluster of tables
在Oracle数据库中,一个数据段(segment)可以供以下方案对象(或方案对象的一部分)容纳数据:
  • 非分区表或非簇表
  • 分区表的一个分区
  • 一个簇表
120 Oracle creates this data segment when you create the table or cluster with the CREATE statement.
 
当用户使用 CREATE 语句创建表或簇表时,Oracle创建相应的数据段(data segment)。
121 The storage parameters for a table or cluster determine how its data segment's extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER statement. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object.
 
表或簇表的存储参数(storage parameter)用来决定对应数据段(data segment)的数据扩展(extent)如何被分配。用户可以使用 CREATEALTER 语句直接设定这些存储参数。这些参数将会影响与方案对象(object)相关的数据段的存储与访问效率。
122
Note:

Oracle creates segments for materialized views and materialized view logs in the same manner as for tables and clusters.
提示:

Oracle 为物化视图(materialized view)及物化视图日志(materialized view log)创建数据段(data segment)的方式与表或簇表相似。
123

See Also:

另见:

124

Introduction to Index Segments

2.4.2 索引段简介

125 Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.
 
Oracle 数据库中每个非分区索引(nonpartitioned index)使用一个索引段(index segment)来容纳其数据。而对于分区索引(partitioned index),每个分区使用一个索引段来容纳其数据。
126 Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage.
 
用户可以使用 CREATE INDEX 语句为索引或索引的分区创建索引段(index segment)。在创建语句中,用户可以设定索引段(index segment)的数据扩展(extent)的存储参数(storage parameter)以及此索引段应存储在哪个表空间(tablespace)中。(表的数据段和与其相关的索引段不一定要存储在同一表空间中。)索引段的存储参数将会影响数据的存储与访问效率。
127

Introduction to Temporary Segments

2.4.3 临时段简介

128 When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes.
 
当Oracle处理一个查询时,经常需要为SQL语句的解析与执行的中间结果(intermediate stage)准备临时空间。Oracle会自动地分配被称为临时段(temporary segment)的磁盘空间。例如,Oracle在进行排序操作时就需要使用临时段。当排序操作可以在内存中执行,或Oracle设法利用索引就执行时,就不必创建临时段。
129

Operations that Require Temporary Segments

2.4.3.1 需要使用临时段的操作

130 The following statements sometimes require the use of a temporary segment:
  • CREATE INDEX
  • SELECT ... ORDER BY
  • SELECT DISTINCT ...
  • SELECT ... GROUP BY
  • SELECT . . . UNION
  • SELECT ... INTERSECT
  • SELECT ... MINUS
以下语句的执行过程中可能会使用临时段(temporary segment):
  • CREATE INDEX
  • SELECT ... ORDER BY
  • SELECT DISTINCT ...
  • SELECT ... GROUP BY
  • SELECT . . . UNION
  • SELECT ... INTERSECT
  • SELECT ... MINUS
131 Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments.
 
有些不能使用索引的关联操作(unindexed join),或者需要在子查询间建立相互关系(correlated subqueries),也可能需要使用临时段(temporary segment)。所以当查询包含DISTINCTGROUP BY,或 ORDER BY 子句时,Oracle有可能使用两个临时段。
132

Segments in Temporary Tables and Their Indexes

2.4.3.2 临时表及其索引使用的段

133 Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.
 
Oracle可以为临时表(temporary table)及其索引分配临时段(temporary segment)。临时表中的数据只在会话(session)或事务(transaction)的运行期内存在。
134

See Also:

"Temporary Tables"

另见:

临时表
135

How Temporary Segments Are Allocated

2.4.3.3 临时段如何被分配

136 Oracle allocates temporary segments differently for queries and temporary tables.
 
Oracle为查询(query)与临时表(temporary table)分配临时段(temporary segment)的过程是不同的。
137

Allocation of Temporary Segments for Queries

2.4.3.3.1 为查询分配临时段

138 Oracle allocates temporary segments as needed during a user session in one of the temporary tablespaces of the user issuing the statement. Specify these tablespaces with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.
 
在用户会话(session)期间,如果用户需要,Oracle可以在用户的临时表空间(temporary tablespace)内为用户创建临时段(temporary segment)。管理员可以使用 CREATE USERALTER USER 语句中的 TEMPORARY TABLESPACE 子句指定用户默认使用的临时表空间。
139

Note:

You cannot assign a permanent tablespace as a user's temporary tablespace.

提示:

用户默认使用的永久表空间(permanent tablespace) 也可以同时被设定为(temporary tablespace)。
140 If no temporary tablespace is defined for the user, then the default temporary tablespace is the SYSTEM tablespace. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment. Oracle drops temporary segments when the statement completes.
 
如果没有为用户指定临时表空间(temporary tablespace),那么默认的临时表空间为 SYSTEM。临时表空间的默认存储参数决定了临时段(temporary segment)的数据扩展(extent)如何分配。在语句结束时Oracle负责移除(drop)临时段。
141 Because allocation and deallocation of temporary segments occur frequently, create at least one special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.
 
因为临时段(temporary segment)的分配与回收经常发生,所以有必要为创建临时段准备一个专用的表空间(tablespace)。这样可以分流磁盘设备的I/O,也可以减少由于在 SYSTEM 或其他表空间内频繁创建临时段而造成的碎片。
142

Note:

When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

提示:

SYSTEM 表空间(tablespace)是本地管理的(locally managed),用户必须在创建数据库的同时定义一个默认的临时表空间(temporary tablespace)。本地管理的 SYSTEM 不能用于默认的临时表空间。
143 Entries for changes to temporary segments used for sort operations are not stored in the redo log, except for space management operations on the temporary segment.
 
因为排序等操作引起的临时段(temporary segment)内的数据变化并不记录到重做日志(redo log)中,只有对临时段进行空间管理的操作才被记录。
144

See Also:

另见:

145

Allocation of Temporary Segments for Temporary Tables and Indexes

2.4.3.3.2 为临时表及临时索引分配临时段

146 Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.
 
当用户首次使用 INSERT 语句向一个临时表(temporary table)插入数据时,Oracle为这个临时表创建临时段(这个插入操作也可能是隐式地由 CREATE TABLE AS SELECT 语句执行)。当首个 INSERT 执行时,Oracle为临时表及其索引分配临时段,创建索引的root page,同时创建所有的 LOB 段(LOB segment)。
147 Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.
 
临时表(temporary table)的段存储在创建者的临时表空间(temporary tablespace)中。
148 Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.
 
当事务(transaction)结束时,Oracle负责移除(drop)属于此事务的临时表(transaction-specific temporary table)使用的段,同样地,当会话(session)结束时,Oracle负责移除(drop)属于此会话的临时表(session-specific temporary table)使用的段。但是如果其他会话或事务也在使用此临时表,此段将继续保存数据。
149

See Also:

"Temporary Tables"

另见:

临时表
150

Introduction to Automatic Undo Management

2.4.4 自动撤销管理简介

151 Oracle maintains information to nullify changes made to the database. Such information consists of records of the actions of transactions, collectively known as undo. Oracle uses the undo to do the following:
  • Rollback an active transaction
  • Recover a terminated transaction
  • Provide read consistency
  • Recovery from logical corruptions
Oracle 中保存了用于恢复对数据库操作的信息。例如事务(transaction)中所有操作的记录(被统称为撤销信息(undo))。Oracle可以利用撤销信息完成以下工作:
  • 回滚(rollback)一个活动的事务
  • 恢复(recover)一个已结束的事务
  • 实现读完整性(read consistency)
  • 恢复语句的逻辑错误(logical corruptions)
152 Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.
 
自动撤销管理(automatic undo management)是基于撤销表空间(undo tablespace)的。用户使用撤销表空间管理撤销信息,而不必创建多个大小不一的回滚段(rollback segment)。
153 Automatic undo management eliminates the complexities of managing rollback segment space and lets you exert control over how long undo is retained before being overwritten. Oracle strongly recommends that you use undo tablespaces to manage undo rather than rollback segments. The system automatically tunes the period for which undo is retained in the undo tablespace to satisfy queries that require undo information. If the current undo tablespace has enough space, then you can set the UNDO_RETENTION parameter to a low threshold value so that the system retains the undo for at least the time specified in the parameter.
 
自动撤销管理(automatic undo management)消除了管理回滚段(rollback segment)的复杂性,使用户只需专注于控制撤销信息(undo)的保存期限。Oracle强烈建议用户使用撤销表空间(undo-tablespace),避免使用回滚段(rollback segment)。系统能够自动调整撤销信息在撤销表空间内的保存时间,供需要使用撤销信息的查询使用。如果正在使用的撤销表空间中有足够的空间,用户可以为 UNDO_RETENTION 参数设定一个最低阀值(low threshold value),这样系统中的撤销信息至少在此值指定的时期内将被保留。
154 Use the V$UNDOSTAT view to monitor and configure your database system to achieve efficient use of undo space. V$UNDOSTAT shows various undo and transaction statistics, such as the amount of undo space consumed in the instance.
 
用户可以使用 V$UNDOSTAT 视图来监视撤销空间(undo space)的使用情况,并对对其进行配置以提高效率。V$UNDOSTAT 能够显示各种与撤销空间及相关事务(transaction)有关的统计信息,例如当前实例(instance)中使用了多少撤销空间。
155

Note:

Earlier releases of Oracle used rollback segments to store undo, also known as manual undo management mode. Space management for these rollback segments was complex, and Oracle has now deprecated that method of storing undo.

提示:

Oracle的早期版本使用回滚段(rollback segment)来存储撤销信息(undo),即手动撤销管理模式(manual undo management mode)。对回滚段进行空间管理十分复杂,Oracle已经放弃了这种撤销信息的管理模式。
156 The Oracle Database contains an Undo Advisor that provides advice on and helps automate the establishment of your undo environment.
 
Oracle数据库提供了 Undo Advisor,能为用户提供建议,并自动地建立用户的撤销环境(undo environment)。
157

See Also:

Oracle Database 2 Day DBA for information on the Undo Advisor and on how to use advisors and see Oracle Database Administrator's Guide for more information on using automatic undo management

另见:

Oracle 数据库 DBA 两日速成 了解 Undo Advisor 及使用帮助,及 Oracle 数据库管理员指南 了解如何使用自动撤销管理(automatic undo management)
158

Undo Mode

2.4.4.1 撤销管理模式

159 Undo mode provides a more flexible way to migrate from manual undo management to automatic undo management. A database system can run in either manual undo management mode or automatic undo management mode. In manual undo management mode, undo space is managed through rollback segments. In automatic undo management mode, undo space is managed in undo tablespaces. To use automatic undo management mode, the database administrator needs only to create an undo tablespace for each instance and set the UNDO_MANAGEMENT initialization parameter to AUTO. You are strongly encouraged to run in automatic undo management mode.
 
用户通过设定撤销管理模式(undo mode)就可以灵活地选择使用手动撤销管理(manual undo management)或自动撤销管理(automatic undo management)。数据库系统既可以运行在手动撤销管理模式下,也可以运行于自动撤销管理模式下。在手动撤销管理模式下,撤销空间通过回滚段(rollback segment)管理。在自动撤销管理模式下,撤销空间通过撤销表空间(undo tablespace)管理。如需使用自动撤销管理模式,数据库管理员只需为每个实例(instance)创建一个撤销表空间,并将初始化参数 UNDO_MANAGEMENT 设为 AUTO 即可。Oracle建议用户使用自动撤销管理模式。
160

Undo Quota

2.4.4.2 撤销空间配额(Undo Quota)

161 In automatic undo management mode, the system controls exclusively the assignment of transactions to undo segments, and controls space allocation for undo segments. An ill-behaved transaction can potentially consume much of the undo space, thus paralyzing the entire system. The Resource Manager directive UNDO_POOL is a more explicit way to control large transactions. This lets database administrators group users into consumer groups, with each group assigned a maximum undo space limit. When the total undo space consumed by a group exceeds the limit, its users cannot make further updates until undo space is freed up by other member transactions ending.
 
在自动撤销管理(automatic undo management)模式下,为事务提供撤销段(undo segment)及为此段分配空间的工作完全由数据库系统控制。然而一个存在问题的事务(ill-behaved transaction)有可能消耗大量撤销空间(undo space),甚至导致整个系统瘫痪。在资源管理器(Resource Manager)中可以设置 UNDO_POOL 参数,从而以更直接的方式控制大事务(large transaction)的管理。数据库管理员可以将用户编为消费者组(consumer group),并为每个组设定最大撤销空间限制。当一个组使用的撤销空间之和超过了设定的限制值时,这个组的用户就不能执行新的更新(update)操作,直到本组内其他用户的事务结束并释放撤销空间。
162 The default value of UNDO_POOL is UNLIMITED, where users are allowed to consume as much undo space as the undo tablespace has. Database administrators can limit a particular user by using the UNDO_POOL directive.
 
UNDO_POOL 参数的默认值是 UNLIMITED,即用户可以使用整个撤销表空间(undo tablespace)来存储其撤销信息。数据库管理员也可以使用 UNDO_POOL 参数对每一个用户进行限制。[另见:Oracle Database Administrator's Guide,Chapter 24 Using the Database Resource Manager,了解关于 UNDO_POOL 的信息。]
 
163

Automatic Undo Retention

2.4.4.3 自动撤销信息保存周期管理

164 Oracle Database 10g automatically tunes a parameter called the undo retention period. The undo retention period indicates the amount of time that must pass before old undo information—that is, undo information for committed transactions—can be overwritten. The database collects usage statistics and tunes the undo retention period based on these statistics and on undo tablespace size. Provided that automatic undo management is enabled, the database automatically tunes the undo retention period as follows:
  • For an AUTOEXTEND undo tablespace, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows. In addition, when there is adequate free space, the tuned retention period does not go below the value of the UNDO_RETENTION initialization parameter.
  • For a fixed size undo tablespace, the database tunes for the maximum possible undo retention. This means always providing the longest possible retention period while avoiding out-of-space conditions and near out-of-space conditions in the undo tablespace. The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.
  • Automatic tuning of undo retention is not supported for LOBs. The tuned retention value for LOB columns is set to the value of the UNDO_RETENTION parameter.
Oracle 10g 数据库自动地调整用于控制撤销信息保存周期(undo retention period)的参数。撤销信息保存周期是指,撤销表空间中旧的撤销信息(即已提交事务的撤销信息)在被覆盖之前至少需要被保存的时间。数据库会收集撤销信息的使用情况,并根据统计结果及撤销表空间(undo tablespace)的大小对撤销信息保存周期进行调整。当数据库处于自动撤销管理(automatic undo management)模式下,其撤销信息保存周期的调整规则如下:
  • 对于存储参数为 AUTOEXTEND 的撤销表空间,如果空间情况允许,数据库将撤销信息保存周期设为比系统中最长的查询时间稍长。此外,如果空间情况允许,系统设定的撤销信息保存周期不会低于 UNDO_RETENTION 初始化参数。
  • 对于固定容量的撤销表空间,数据库将撤销信息保存周期设为撤销表空间所支持的最大值。这意味着数据库可以使用接近撤销表空间极限的容量为用户提供最长的撤销信息保存周期。The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.
  • 撤销信息保存周期的自动调整不适用于 LOB 数据。LOB 列的撤销信息保存周期由 UNDO_RETENTION 参数决定。
165 For fixed size and AUTOEXTEND undo tablespaces of equal size, depending on the queries that you run, the tuning method used in fixed size tablespaces tends to provide a longer retention period. This enables flashback operations to flash back farther in time, and maximizes the amount of undo data available for long-running queries.
 
当一个固定容量的撤销表空间(undo tablespace)与一个自动扩展的撤销表空间(存储参数为 AUTOEXTEND)容量相同时,前者使用的撤销信息保存周期(undo retention period)调整方法通常能够提供更长的保存时间。这使回闪(flashback)操作可以追溯的更远,也使运行时间长的查询有更多的撤销数据(undo data )可用。
166

External Views

2.4.4.4 外部视图

167 Monitor transaction and undo information with V$TRANSACTION and V$ROLLSTAT. For automatic undo management, the information in V$ROLLSTAT reflects the behaviors of the automatic undo management undo segments.
 
用户可以使用 V$TRANSACTIONV$ROLLSTAT 视图监控事务(transaction )及撤销空间(undo)的信息。对于自动撤销管理(automatic undo management)模式,V$ROLLSTAT 视图能够展现自动撤销管理所使用的各个撤销段(undo segment)的情况。
168 The V$UNDOSTAT view displays a histogram of statistical data to show how well the system is working. You can see statistics such as undo consumption rate, transaction concurrency, and lengths of queries run in the instance. Using this view, you can better estimate the amount of undo space required for the current workload.
 
V$UNDOSTAT 可以显示撤销空间运行的历史统计信息。用户可以查询撤销空间使用率(undo consumption rate),事务并发性(transaction concurrency),实例中最长的查询的运行时间等统计信息。通过这个视图,用户可以更好地估计在当前工作负荷下系统所需的撤销空间(undo space)容量。
169

See Also:

Oracle Database Administrator's Guide for more details about setting UNDO_MANAGEMENT, automatic tuning of undo retention, and using V$UNDOSTAT

另见:

Oracle 数据库管理员指南 详细了解如何设置 UNDO_MANAGEMENT 参数,撤销信息保存周期自动调整,以及如何使用 V$UNDOSTAT 视图等信息

翻译不确定的词汇(格式:红色)  

 

翻译不确定的技术性词汇(格式:
红色

 

翻译不确定的Oracle词汇(格式:
红色

[012] free list 可用块列表
[022] common and variable header
[022] table directory
[022] row directory
[022] free space
[022] row data
[028] row piece
[034] Row Chaining and Migrating
[037] transaction entries
[040] Automatic segment-space management
[077] initial extent
[078] incremental extent
[083] permanent tablespaces
[092] Segment Advisor
[094] owner
[109] single sort
[110] Multiple sort
[110] sort segments
[146] root page
[156] Undo Advisor
[161] Resource Manager
[165] flashback

翻译不确定的句子(格式:
红色

[040] Better run-time adjustment to variations in concurrent access
[040] Better multi-instance behavior in terms of performance/space utilization
[164] The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.

注释性的文字(格式:
[绿色]

[028]
[039]
[040]
[162]

相关链接

1、DROP/TRUNCATE时Extent的回收情况
http://www.itpub.net/514580.html

2、drop index 后,如何回收空间
http://www.itpub.net/507280.html

3、9.2.0的UNDO表空间不能自动释放
http://www.itpub.net/515307.html

4、ITPUB提问
http://www.itpub.net/showthread.php?s=&threadid=515368

5、表空间这样创建是不会自动扩展的吗?
http://www.itpub.net/482386.html

未完成的链接


[017] Multiple Block Sizes
[042] Oracle Database Administrator's Guide
[050] Row Format and Size
[050] Rowids of Row Pieces
[050] Physical Rowids
[050] Oracle Database Performance Tuning Guide
[053] Overview of LOB Datatypes
[086] Managing Space in Tablespaces
[086] Bigfile Tablespaces
[086] Oracle Database Administrator's Guide
[093] Oracle Database Administrator's Guide
[093] Oracle Database SQL Reference
[096] Oracle Database Administrator's Guide
[096] Oracle Database SQL Reference
[105] Overview of Materialized Views
[112] Introduction to Temporary Segments
[112] Temporary Tables
[123] Oracle Database Advanced Replication
[123] Oracle Database SQL Reference
[134] Temporary Tables
[144] Bigfile Tablespaces
[144] Database Security
[149] Temporary Tables
[157] Oracle Database 2 Day DBA
[157] Oracle Database Administrator's Guide
[169] Oracle Database Administrator's Guide

Oracle学习问题(格式:
黄色背景
1、关于高水位线及数据块的分配,有待更详细的内容
[012] If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not allocated to a specific instance, then the blocks themselves are allocated only when the high water mark moves. The high water mark is the boundary between used and unused space in a segment.

2、有待验证
[015] In contrast, at the physical, operating system level, all data is stored in bytes.

3、有待学习
[015] Each operating system has a block size.

4、为什么会引起unnecessary I/O;maximum limit是多少?
[016] The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O.

5、数据块头的内容、格式
[024] The header contains general block information, such as the block address and the type of segment (for example, data or index).

6、哪些管理开销固定,哪些可变?
[031] Some block overhead is fixed in size; the total block overhead size is variable.

7、locally managed/dictionary managed管理的对象是什么?可用块列表的管理的对象是什么?
[040] The in-segment free/used space is tracked using bitmaps, as opposed to free lists.
答:locally managed/dictionary managed方式管理的对象是data block,可用块列表(free list)的管理的对象也是data block。但二者的管理目的不同。前者是为了找出可用块形成extent这种逻辑存储结构;后者是为了找出可用块向其中写入数据。

7.1、又出现新问题了。如果locally managed/dictionary managed方式管理的对象是可用/已用data block,难道磁盘上已经存在了很多data block?

8、被迁移的数据行,在原数据块中的空间是否释放?
[048] Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row.

9、含义不清
[100] Coalescing extents is not necessary in locally managed tablespaces, because all contiguous free space is available for allocation to a new extent regardless of whether it was reclaimed from one or more extents.

10、含义不清
[111] A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.
答:解决

11、含义不清。为什么例子中说可能使用俩个临时段?
[131] For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments.

12、root page是什么?LOB segment又是什么?
[146] The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

13、临时表不是私有的么?
[148] If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.

14、什么是retention guarantee?
[164] The UNDO_RETENTION initialization parameter is ignored unless retention guarantee is enabled.

15、一个data block中包含的数据只可能属于一个表吧?
[026] This portion of the data block contains information about the table having rows in this block.

16、自动段空间管理(automatic segment-space management)的详细信息,如设置方式,参数等。
[041]

translator: zw1840@hotmail.com