TABLESPACE

Tablespace是最大的逻辑存储结构，它把HWM以下block标号，即表空间下segment共用block id; 每个segment又把其extent标号，每个segment下的extent id均从0开始。

以上标号均为逻辑顺序不表示物理存储地址连续，block是最小的逻辑单位，它由多个物理block组成，物理block本身就可能不连续

SET LINEISZE 200

COL SEGMENT_NAME FORMAT A20

select segment_name, extent_id, block_id,  blocks from dba_extents where tablespace_name ='USERS' order by block_id;

SEGMENT_NAME          EXTENT_ID   BLOCK_ID     BLOCKS

-------------------- ---------- ---------- ----------

LIN                           0        128          8

T1                            0        136          8

T1                            1        144          8

...

T1                           16        384        128

T1                           17        512        128

（一）Tablespace Space Management

When a database object requires an extent, the database must have a method of finding and providing it. Similarly, when an object no longer requires an extent, the database must have a method of making the free extent available.

管理TABLESPACE中extent空间有两种方式：LOCAL与DICTIONARY

You can create either of the following types of tablespaces:

1. Locally managed tablespaces (default)

The database uses bitmaps in the tablespaces themselves to manage extents. Within a tablespace, the database can manage segments with automatic segment space management (ASSM) or manual segment space management (MSSM).

1. Dictionary-managed tablespaces

The database uses the data dictionary to manage extents.

note: 关于表空间扩展extent的默认方式查看EXTENT说明

1. Locally Managed Tablespaces

local管理extent的方式是在datafile header中存储bitmaps，0表示free, 1表示used,每个bit代表a group of blocks.

A locally managed tablespace maintains a bitmap in the data file header to track free and used space in the data file body.

Each bit corresponds to a group of blocks. When space is allocated or freed, Oracle Database changes the bitmap values to reflect the new status of the blocks.

The following graphic is a conceptual representation of bitmap-managed storage. A 1 in the header refers to used space, whereas a 0 refers to free space.

A locally managed tablespace has the following advantages:

1. Avoids using the data dictionary to manage extents

Recursive operations can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or undo segment.

1. Tracks adjacent free space automatically
2. In this way, the database eliminates the need to coalesce free extents.
3. Determines the size of locally managed extents automatically

Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

Local管理tablespace extent方式下，又分两种不同方式来管理SEGMENT中的blocks:

1. Automatic Segment Space Management(ASSM)

通过segment header存储bitmaps以及pctfree来管理segment的blocks，它不使用PCTUSED

segment header有5个位图（对应段头的5个blocks,分别对应满度为100%，75-100%,50-75%,25-50%和0-25%的块. 即autoallocate比manual每个段多出5个blocks用于存bitmaps）用于描述每个块的满度，根据满度的不同将每个块对应到相应的位图上，当block发生变化会更新位图，以反应数据块是否可插入

ASSM is more efficient and is the default for permanent, locally managed tablespaces.

ASSM method uses bitmaps to manage space in a tablespace.

ASSM avoids the need to manually determine correct settings for many storage parameters. Only one crucial SQL parameter controls space allocation: PCTFREE.

1. Manual Segment Space Management

MSSM使用freelist以及PCTFREE和PCTUSED管理blocks

The legacy manual segment space management (MSSM) method uses a linked list called a free list to manage free space in the segment.

For a database object that has free space, a free list keeps track of blocks under the high water mark (HWM).

In addition to PCTFREE, MSSM requires you to control space allocation with SQL parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS. PCTUSED sets the percentage of free space that must exist in a currently used block for the database to put it on the free list. For example, if you set PCTUSED to 40 in a CREATE TABLE statement, then you cannot insert rows into a block in the segment until less than 40% of the block space is used.

Managing segment space manually can be complex. You must adjust PCTFREE and PCTUSED to reduce row migration and avoid wasting space.

一个segment可有多个free list，用于减少争用，但同一session只能使用一个free list

RAC可以使用free list group, 它把segment的free block分组，来减少实例间的segment free block元信息交换，详见extent说明

An object may have multiple free lists. In this way, multiple sessions performing DML on a table can use different lists, which can reduce contention. Each database session uses only one free list for the duration of its session.

As shown in Figure 12-5, you can also create an object with one or more free list groups, which are collections of free lists. Each group has a master free list that manages the individual process free list in the group.

1. Dictionary-Managed Tablespaces

A dictionary-managed tablespace uses the data dictionary to manage its extents. 

extent分配或回收是通过数据库中的数据字典表UET$(used extents)和FET$(freeextents)来记录和管理的，当建立一个新的段或者段在表空间时，oracle通过一系列的SQL更新数据字典，这在繁忙的系统中会造成竞争和等待，同时这种方式也会产生空间碎片

Oracle Database updates tables in the data dictionary whenever an extent is allocated or freed for reuse. For example, when a table needs an extent, the database queries the data dictionary tables, and searches for free extents. If the database finds space, then it modifies one data dictionary table and inserts a row into another. In this way, the database manages space by modifying and moving data.

The SQL that the database executes in the background to obtain space for database objects is recursive SQL. Frequent use of recursive SQL can have a negative impact on performance because updates to the data dictionary must be serialized.

dictionary管理extent方式，对于segment的block只能使用manual管理，即使用freelist.

（二）Permanent Tablespaces

1. The SYSTEM Tablespace

The SYSTEM tablespace includes the following information, all owned by the SYS user:

The data dictionary

Tables and views that contain administrative information about the database

Compiled stored objects such as triggers, procedures, and packages

SYSTEM如果是locally managed, 此时是无法创建dictionary-managed表空间

In a database with a locally managed SYSTEM tablespace, you cannot create dictionary-managed tablespaces (which are deprecated). However, if you execute the CREATE DATABASE statement manually and accept the defaults, then the SYSTEM tablespace is dictionary managed. You can migrate an existing dictionary-managed SYSTEM tablespace to a locally managed format.

1. The SYSAUX Tablespace

If the SYSAUX tablespace becomes unavailable, then core database functionality remains operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

1. Undo Tablespaces

1. Automatic Undo Management Mode

1. 如果undo_management=manual, 则使用manual undo managment

manual的方式早在10g已淘汰，它是在每个表空间各自存储自己的rollback segments来管理undo的,不需要undo tablespace.事务开始时由数据库或手动指定使用的undo segment:

select SEGMENT_ID ,SEGMENT_NAME, TABLESPACE_NAME from dba_rollback_segs;

create rollback segment rbs_one;  

select SEGMENT_ID ,SEGMENT_NAME, TABLESPACE_NAME from dba_rollback_segs;

set transaction use rollback segment rbs_one;    

commit;

drop rollback segment rbs_one;

1. 如果undo_management=auto则使用automatic undo managment

Automatic mode eliminates the complexities of manually administering undo segments. The database automatically tunes itself to provide the best possible retention of undo data to satisfy long-running queries that may require this data.

B.1如果数据库有一个或多个undo tablespace，但未设置undo_tablespace参数，则oracle会自动选择一个undo tablespace使用；如果设置了则使用undo_tablespace设置值

A database can contain multiple undo tablespaces, but only one can be in use at a time. When an instance attempts to open a database, Oracle Database automatically selects the first available undo tablespace.

B.2如果此时数据库没有undo tablespace，则使用system作undo tablespace

If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo data in the SYSTEM tablespace.

1. Automatic Undo Retention

Undo tablespace中的undo data分为三种：

1) Uncommitted undo information: 未提交回滚数据，数据永远不能被其它事务覆盖

1. Committed undo information: 已经提交但未过期的回滚数据，undo data仍未超过Oracle调优的retention时长，这部分数据还会用于long query的一致性读以及闪回
   3) Expired undo information: 已经提交且超过retention时长，同样用于一致性读和闪回。

Oracle会根据统计信息及undo tablespace size自动调优undo retention, 如果undo tablespace为aotuextend会扩展undo tablespace大小使undo retention会大于longest-running query

In general, it is desirable to retain old undo data as long as possible. After a transaction commits, undo data is no longer needed for rollback or transaction recovery. The database can retain old undo data if the undo tablespace has space for new transactions. When available space is low, the database begins to overwrite old undo data for committed transactions.

Oracle Database automatically provides the best possible undo retention for the current undo tablespace. The database collects usage statistics and tunes the retention period based on these statistics and the undo tablespace size.

If the undo tablespace is configured with the AUTOEXTEND option, and if the maximum size is not specified, then undo retention tuning is different. In this case, the database tunes the undo retention period to be slightly longer than the longest-running query, if space allows.

Oracle调优后的retention为实现使用大小，通过v$undostat的tuned_undoretention查看，undo_retention参数只是设置最少undo data在提交后保留秒数，默认为900秒

注如果不设置RETENTION GUARANTEE则undo_retention参数只在undo tablespace为autoextend时才生效，而且只在undo tablespace有足够空间时才能保证，如果没有足够空间还是会覆盖掉Committed undo information

The setting of this parameter should account for any flashback requirements of the system.

For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries.

For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

Note: Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.

The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space.

RETENTION GUARANTEE设置后可以保证undo_retention内的undo data不被覆盖，可能会因数undo空间不足导致无法DML，但可以DDL:

Show parameter undo

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE | NOGUARANTEE;
select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME                RETENTION

------------------------------ -----------

UNDOTBS1                       NOGUARANTEE

如果long query使用到committed undo information/expired undo information时，而相关undo data已被覆盖将会产生"snapshot too old"的错误信息。如果undo tablespace非guarantee则可能是undo表空间太小(与undo_retention设置无关)，或是有大事务长时间未提交(事务中有查询操作需要一致性快照，非事务内的查询只需要查询时的一致性快照, 如果查询时间不是很长不会产生这种报错)

（三）Temporary Tablespaces    ---关于local 的SQL REFRENCE再看下

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。

当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。这里释放仅是将这些空间标记为空闲，真正占用的磁盘空间并没有释放，所以Temp表空间可能会越来越大。

When processing a query, Oracle Database often requires temporary workspace for intermediate stages of SQL statement execution.

Typical operations that may require a temporary segment include sorting, hashing, and merging bitmaps. While creating an index, Oracle Database also places index segments into temporary segments and then converts them into permanent segments when the index is complete.

Oracle Database does not create a temporary segment if an operation can be performed in memory. However, if memory use is not possible, then the database automatically allocates a temporary segment on disk.

Oracle Database allocates temporary segments for queries as needed during a user session and drops them when the query completes.

Temp files are always set to NOLOGGING mode, which means that they never have redo generated for them. Media recovery does not recognize temp files. 但临时对象会产生undo，undo的操作会记录redo, 12c可以配置TEMP_UNDO_ENABLED参数还避免这种情况的redo.

You cannot make a temp file read-only.

Temp tablespace扩展extent默认为UNIFORM，其它表空间默认为AUTOALLOCATE

1. Shrinking a Locally Managed Temporary Tablespace

Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse.

Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.

Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

Alter tablespace temp SHRINK SPACE;                     --物理文件也收缩了

Alter tablespace temp SHRINK SPACE KEEP 250M;           --总temp物理文件收缩到250M

Alter tablespace temp SHRINK SPACE TEMPFILE tempfile '/oradata/orcl/temp2.dbf'  KEEP 250M;

1. Shared and Local Temporary Tablespaces

12.2提出了local temporary tablespace新概念，之前均为shared temporary tablespace

Temporary tablespaces are either shared or local:

A shared temporary tablespace stores temp files on shared disk, so that the temporary space is accessible to all database instances. In contrast, a local temporary tablespace stores separate, non-shared temp files for every database instance. Local temporary tablespaces are useful for Oracle Real Application Clusters or Oracle Flex Clusters.

Note:

Local temporary tablespaces are new in Oracle Database 12c Release 2 (12.2). In previous releases, shared temporary tablespaces were simply called temporary tablespaces.

You can create local temporary tablespaces for both read-only and read/write database instances. When many read-only instances access a single database, local temporary tablespaces can improve performance for queries that involve sorts, hash aggregations, and joins.

The advantages are:

Improving I/O performance by using local rather than shared disk storage

Avoiding expensive cross-instance temporary space management

Improving instance startup performance by eliminating on-disk space metadata management

The following table compares the characteristics of shared and local temporary tablespaces.

1. Default Temporary Tablespaces

可以在数据库级与用户级设置默认的shared/local temporary tablespace

Every database user account is assigned a default shared temporary tablespace.

If the database contains local temporary tablespaces, then every user account is also assigned default local temporary storage.

A user query can access either shared or local temporary storage. Furthermore, a user could have one default local temporary tablespace assigned for read-only instances, and a different default local temporary tablespace assigned for read/write instances.

For read/write instances, the database gives higher priority to shared temporary tablespaces. For read-only instances, the database gives higher priority to local temporary tablespaces. If the database instance is read/write, then the database searches for space in the following order:

1. Is a shared temporary tablespace assigned to the user?
2. Is a local temporary tablespace assigned to the user?
3. Does the database default temporary tablespace have space?

If the answer to any preceding question is yes, then the database stops the search and allocates space from the specified tablespace; otherwise, space is allocated from the database default local temporary tablespace.

If the database instance is read-only, then the database searches for space in the following order:

1. Is a local temporary tablespace assigned to the user?
2. Does the database default local temporary tablespace assigned have space?
3. Is a shared temporary tablespace assigned to the user?

If the answer to any preceding questions is yes, then the database stops the search and allocates space from the specified tablespace; otherwise, space is allocated from the database default shared temporary tablespace.

（四）Tablespace Modes

The SYSTEM and SYSAUX tablespaces and temporary tablespaces are permanently read/write, which means that they cannot be made read-only.

The SYSTEM tablespace and temporary tablespaces cannot be taken offline.

OFFLINE过程：

1. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level.
2. The database saves undo data corresponding to those completed statements in a deferred undo segment in the SYSTEM tablespace. When the tablespace is brought online, the database applies the undo data to the tablespace, if needed.