文档来源：

<>P142,P148

<>P332

<>582

Temporary Tablespace

目录

1 临时表空间:

2 临时段

3 临时表，

4 相关视图

5 Temporary Tablespaces 的三种类型

6 Notes

1 Temporary Tablespace 临时表空间

1.1   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. However, in DMT system can be used as temporary storage.(LMT,system tablespace can’t be used as temporary tablespace)

1.2   If you drop the default temporary tablespace, then the SYSTEM tablespace is used as the default temporary tablespace.(1在LMT中呢，如果删掉temp,情况会怎么样？报错) ,You cannot make the default temporary tablespace permanent or take it offline.

1.3   A temporary tablespace can be used only for sort segments.A temporary tablespace is not the same as a tablespace that a user designates for temporary segments. Which can be any tablespace available to the user. No permanent schema objects can reside in a temporary tablespace.(2临时表空间可以用来只存储sort segments, 与temporary segments是不同的，temporary segment 能够在任何表空间。临时表呢，放在哪个表空间？temporary tablespace)

1.4   Sort segments are used shared by multiple sort operation. One sort segment exists for every instance that performs a sort operation in a given tablespace. Sort segments are allocated only once for the instance, and they are not returned after the sort. But remain available for other multiple sorts. Within a temporary tablespace, all sort opearations for a given instance and tablespace share a single sort segment. The sort segment is created by the first statement that uses a temporary tablespace for sorting and is released only at shutdown. (sort segment only released at shutdown 这里如何理解？)

1.5 临时表空间的两个作用

    Disk sort and Global Temporary table.

2 Temporary Segments

2.1 Introduction to Temporary Segments

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use. (Cleaned up by SMON Background Process 由SMON进程来释放空间)

2.2 Operations that Require Temporary Segments

The following statements sometimes require the use of a temporary segment:

l         Create Index

l         Select … Order By

l         Select Distinct ….

l         Select … Group By

l         Select … Union

l         Select … Intersect

l         Select … Minus

l         Analyze table

l         Unindexed joins

l         Certain correlated subqueries

2.3 Segments in Temporary Tables and Their Indexes

    Oracle can allocate temporary segments for temporary tables and indexes created on temporary tables.(临时表，临时表的索引都建在Temporary Segments)

2.4 How Temporary Segments are allocated

Oracle allocates temporary segments differently for queries and temporary tables.

2.4.1 Queries

Oracle allocates temporary segments as needed during a user session in the temporary tablespace of the user issuing the statement need temporary segments. Oracle drops temporary segments when the statement completes(执行语句的时候分配临时表空间,执行完,退出session,后删除.)

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

If no temporary tablespace is defined for the user, then the default temporary tablespce is the SYSTEM tablespace.(in LMT 也是这样吗? 不是的，在LMT中，建库时必须指定一个Temporary tablespace.所有用户的default temporary tablespace 都使用这个)

测试

2.4.2 Temporary table

Oracle allocates segments for a temporary table when the first INSERT into that table is issued. 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. (第一个Insert执行时分配。事务或session结束后删除)

2.5 Extents in Temporary Segments

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 the temporary tablespace of the user issuing the statement and then returns the extents to the tablespace.(对于用户来说，可以使用单独的临时表空间，而不是默认的表空间)

Multiple sorts, however, can use sort segments in a temporary tablespace 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.

2.6 查看临时段信息

3 临时表 temprorary table

    临时表的创建与使用参考其它文档<>

4 相关视图

 V$sort_segment, v$tempseg_usage

5 Temporary Tablespaces 的三种类型

The various tablespaces can be used for disk sorting including the following:

 Temporary Tablespaces

Tablespaces of Type Temporary

Permanent Tablespaces

6 Notes

1  一般不能让临时表空间无限制地自动增长

2  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.(1 临时段的变化不产生redo,但对临时段的undo好像会产生redo,没测试过? 2 临时段除了用于sort operations，还会用于哪些操作呢？temporary table )

3 临时段并不只在临时表空间中，在普通表空间中也有。(如何测试？)

Temporary segments can be stored in any tablespace. However, it makes more sense to store temporary segments in a temporary tablespaces.

4 临时表空间不能释放问题?

5 Temporary segment 与 Sort Segment 的关系？子集关系?

6 Disk sort and PGA sort oracle 临时表空间与pga中的排序区的区别

当有排序操作时，先在PGA中使用sort area区，如果sort area空间不够，就使用临时表空间。
理解disk sort的真正机制.参考另一篇文档<的研究与测试_20091130.doc>>