V$TEMPSEG_USAGE 中SEGTYPE的理解

今天用到视图V$TEMPSEG_USAGE，看到有好多对象类型，发现有如下几种临时sort segment type:

SEGTYPE

VARCHAR2(9)

Type of sort segment: SORT  排序 HASH  hash join（unindexed joins） DATA  temporary tables INDEX  temporary indexs LOB_DATA   LOB_INDEX

去官方文档查询segment 创建：

https://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#BEGIN

2 Data Blocks, Extents, and Segments

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

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 Database can require as many as two temporary segments.

Segments in Temporary Tables and Their Indexes

Oracle Database 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.

Allocation of Temporary Segments for Queries

Oracle Database 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.

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 Database drops temporary segments when the statement completes.

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.

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.

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.

Allocation of Temporary Segments for Temporary Tables and Indexes

Oracle Database 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.

Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.

Oracle Database 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.

V$TEMPSEG_USAGE其实是v$sort_usage的同义词：