OracleDB入门3：临时表空间（组）

本文转载于ORACLE临时表空间总结

概念

临时表空间

临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、UNION ALL、INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。

当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。

临时表空间存储大规模排序操作和散列操作的中间结果。它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。

创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。

另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。

临时表空间组

临进表空间组是ORACLE 10g引入的一个新特性，它是一个逻辑概念，不需要显式的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。

A temporary tablespace group is a tablespace group that is assigned as the default temporary tablespace for the database.

临时表空间组是一个用于整个数据库的默认表空间组。

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

表空间组允许用户消耗多个表空间的空间。使用表空间组可以缓解单独一个表空间不足以存储所有排序结果的问题。表空间组允许在单一操作的并行执行服务使用多个临时表空间。

A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces

一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制.如果删除一个临时表空间组的所有成员，该组也自动被删除。

It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.

可以在创建临时表空间是指定表空间组，即隐式创建。

相关操作

临时表空间

1. 查看实例的临时表空间

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

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

或：

SQL> SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

2. 查看临时表空间信息：

SQL> SET LINESIZE 1200
SQL> COL NAME FOR A60
SQL> SELECT FILE#                        AS FILE_NUMBER
    ,NAME                           AS NAME
    ,CREATION_TIME                  AS CREATION_TIME
    ,BLOCK_SIZE                     AS BLOCK_SIZE
    ,BYTES/1024/1024/1024           AS "FILE_SIZE(G)"
    ,CREATE_BYTES/1024/1024/1024    AS "INIT_SIZE(G)"
    ,STATUS                         AS STATUS
    ,ENABLED                        AS ENABLED
FROM V$TEMPFILE;

说明：主要是查看V$TEMPFILE这个视图，官方文档关于V$TEMPFILE的介绍如下

Column	Datatype	Description
FILE#	NUMBER	Absolute file number
CREATION_CHANGE#	NUMBER	Creation System Change Number (SCN)
CREATION_TIME	DATE	Creation time
TS#	NUMBER	Tablespace number
RFILE#	NUMBER	Relative file number in the tablespace
STATUS	VARCHAR2(7)	Status of the file (OFFLINE|ONLINE)
ENABLED	VARCHAR2(10)	Enabled for read and/or write
BYTES	NUMBER	Size of the file in bytes (from the file header)
BLOCKS	NUMBER	Size of the file in blocks (from the file header)
CREATE_BYTES	NUMBER	Creation size of the file (in bytes)
BLOCK_SIZE	NUMBER	Block size for the file
NAME	VARCHAR2(513)	Name of the file

 

3. 查看临时表空间使用率：

SQL> SELECT BYTES,BLOCKS,  USER_BYTES, USER_BLOCKS, BLOCKS -USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES;

     BYTES     BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED
---------- ---------- ---------- ----------- -----------
2147483648     262144 2146435072      262016         128
1073741824     131072 1072693248      130944         128
 209715200      25600  208666624       25472         128

这四列中， BYTES , BLOCKS 显示的是临时文件有多少BYTE大小，包含多少个数据块。而USER_BYTES,USER_BLOCKS是可用的BYTE和数据块个数。因此，我们可以知道临时文件中有一部分是被系统占用的，大概可以理解成文件头信息，这一部分大小是128个block。

4. 创建临时表空间

SQL> CREATE TEMPORARY TABLESPACE TMP  TEMPFILE '/u01/gsp/oradata/TMP01.dbf' SIZE 8G AUTOEXTEND OFF;

5. 给临时表空间增加数据文件

当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf' SIZE 4G AUTOEXTEND ON NEXT 128M MAXSIZE 6G;

Tablespace altered.

 或

SQL> ALTER  TABLESPACE TMP ADD TEMPFILE '/u03/eps/oradata/temp02.dbf' SIZE 64G AUTOEXTEND OFF;

6. 删除数据文件

例如，我想删除临时表空间下的某个文件，那么我们有两种方式删除临时表空间的数据文件。

方法1：

SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';
Tablespace altered.

注意：这种删除临时表空间的写法会将对应的物理文件删除。 

方法2：

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' DROP INCLUDING DATAFILES;
Database altered.

7. 调整文件大小

如下例子，需要将临时数据文件从1G大小调整为2G

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;

8. 文件脱机联机

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
Database altered.
SQL> ALTER DATABASE TEMPFILE  '/u01/app/oracle/oradata/GSP/temp02.dbf' ONLINE;
Database altered.

注意：默认临时表空间并不能脱机,否则会报错。

SQL> ALTER TABLESPACE TEMP OFFLINE;
ALTER TABLESPACE TEMP OFFLINE
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

9. 设置文件自动扩展

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

临时表空间组

1. 创建临时表空间并加入组

SQL>CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M TABLESPACE GROUP GRP_TEMP;

2. 查看临时表空间组：

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME                            TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP                                  TEMP2

3. 指定已经创建好的临时表空间的临时表空间组

SQL> ALTER TABLESPACE  TEMP TABLESPACE GROUP GRP_TEMP;
    Tablespace altered.

自学

数据库异地恢复后，临时表空间的路径不正确，以下是完整的解决方案。

• 查看临时表空间

  select TABLESPACE_NAME from   dba_tablespaces;
  select * from v$tempfile;

  
  
  发现路径是错的。
• 创建新的临时表空间

create temporary TABLESPACE XXXXX TEMPFILE '/data/T24APDB/datafile/CMBASOC_TEMP.dbf'  SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 5152M

• 修改数据库默认临时表空间（将修改所有schema的临时表空间）

alter database default temporary tablespace CMBASOC_TEMP;

• 删除旧临时表空间

drop tablespace  TEMP including contents and datafiles;
drop tablespace DB_TEMP_ZXHK including contents and datafiles;

• 再次查看临时文件（删除成功）

select * from v$tempfile;

总结

 本人是在完成数据库异地恢复后发现临时表空间路径问题（路径根本不存在），导致查询大表时无法排序。因而搜索得到本文，理解不够深入，还望大佬指点。