临时表空间包含的内容以及作用

1. 中间排序的结果
2. 临时表和临时索引
3. 临时LOBs
4. 临时B-trees

临时表空间包含一些临时数据，只是短暂使用，一般排序操作用到临时表空间的时候比较多，还有就是创建临时表的时候。数据库启动后默认会创建一个叫TEMP的临时表空间，数据库关闭后，临时表空间的内容自动释放掉，也可以使用create tablespace语句手动创建临时表空间。一个临时表空间可以由多个用户共享使用。

使用以下语句可以修改数据库的临时表空间：

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

以下语句可以查询数据库的默认表空间的名称

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
   PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME              PROPERTY_VALUE
-------------------------- ------------------------------
DEFAULT_TEMP_TABLESPACE    TEMP

V$SORT_SEGMENT 视图查看临时表空间排序段中分配和未分配的extents。 V$TEMPSEG_USAGE标识了这些extents是那些用户使用的。如下一个查询的实例：

SQL> select TABLESPACE_NAME,USED_EXTENTS,FREE_EXTENTS ,TOTAL_EXTENTS from V$SORT_SEGMENT;

TABLESPACE_NAME                 USED_EXTENTS FREE_EXTENTS TOTAL_EXTENTS
------------------------------- ------------ ------------ -------------
TEMP                                       3        35762         35765

SQL> desc V$TEMPSEG_USAGE 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 USER                                               VARCHAR2(30)
 SESSION_ADDR                                       RAW(8)
 SESSION_NUM                                        NUMBER
 SQLADDR                                            RAW(8)
 SQLHASH                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 TABLESPACE                                         VARCHAR2(31)
 CONTENTS                                           VARCHAR2(9)
 SEGTYPE                                            VARCHAR2(9)
 SEGFILE#                                           NUMBER
 SEGBLK#                                            NUMBER
 EXTENTS                                            NUMBER
 BLOCKS                                             NUMBER
 SEGRFNO#                                           NUMBER

SQL> select username,user,extents from v$tempseg_usage;

USERNAME                       USER                              EXTENTS
------------------------------ ------------------------------ ----------
SYS                            SYS                                     1
SYS                            SYS                                     1
SYS                            SYS                                     1

当一个排序操作完成后，不会释放这写extents,而是标记为free,然后给其他排序操作重用。可以查询DBA_TEMP_FREE_SPACE看到总的分配的空间和空闲的空间。也可以手工收缩本地管理的临时表空间。以下两条命令是收缩表空间和临时表空间数据文件的语句，keep选项指定的尺寸，如果省略不写，则会收缩到最小。

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;
ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

创建一个本地管理的临时表空间

可以查询 V$TEMPFILE and DBA_TEMP_FILES 视图，查看临时表空间的数据文件。

以下语句表示一个extent是16M(如果块是2K，那么就是8000个块)，大小为20M的临时表空间

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' 
     SIZE 20M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

临时表空间组

临时表空间组介绍：

1. 至少包含一个临时表空间，组中最大包含的临时表空间个数没有限制
2. 共享表空间的命名，所以不能和任何表空间的名字相同
3. 在为数据库分配默认临时表空间或为用户分配临时表空间时，可以在任何位置指定表空间组名称。

视图DBA_TABLESPACE_GROUPS可以查看其相关信息。
如下示例，如果group1和group2都不存在，可以使用create语句创建通过tablespace group指定一个组，也可以通过alter tablespace语句指定一个组。

CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
     SIZE 50M
     TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

可以通过以下语句添加临时表空间到临时表空间组，以下语句把Imtemp3添加到了group1中，结合上面的语句，group1中包含了lmtemp2和lmtemp3

CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
     SIZE 25M
     TABLESPACE GROUP group1;
     
Imtemp2从group1移到了group2  
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

现在group2包含了Imtemp和Imtemp2

从组中删除临时表空间

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

分配一个临时表空间组为默认的临时表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;

如果指定了一个临时表空间组为数据库默认的临时表空间，那么不能直接删除临时表空间组的成员，必须先从临时表空间组移除才可以。同样，也不能删除数据库中默认的单个的临时表空间。

创建临时表

临时表分为session级和事物级，临时表对所有session都可见，但是数据只对插入的session才可见。

使用CREATE GLOBAL TEMPORARY TABLE语句创建一个临时表。 ON COMMIT子句表示表中的数据是事务级（缺省值）还是会话级，其含义如下：

解释：
DELETE ROWS 创建基于事务的临时表。会每次提交后，数据库都truncate表（删除所有行）。
PRESERVE ROWS 创建一个基于session的临时表，当session结束时，truncate表。

如下创建一个事物临时表：

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS;

也可以在临时表上创建索引，和临时表类型一致，可以基于事物和会话两种。

默认情况下，临时表中的行存储在创建它的用户的默认临时表空间中。但是，创建临时表后，可以使用CREATE GLOBAL TEMPORARY TABLE的TABLESPACE子句将临时表分配给另一个表空间。可以使用此功能来节省临时表使用的空间。这样排序操作和临时表就分开存放，避免不必要的冲突和性能影响。

如下：创建一个tbs_t1的临时表空间，然后给临时表admin_work_area使用

CREATE TEMPORARY TABLESPACE tbs_t1 
    TEMPFILE 'tbs_t1.f' SIZE 50m REUSE AUTOEXTEND ON
    MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

CREATE GLOBAL TEMPORARY TABLE admin_work_area
        (startdate DATE,
         enddate DATE,
         class CHAR(20))
      ON COMMIT DELETE ROWS
      TABLESPACE tbs_t1;