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等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。
临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。
创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。
另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。
临时表空间信息
查看实例的临时表空间
SQL1：
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------
DEFAULT_TEMP_TABLESPACE TEMP
SQL2：
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
查看临时表空间信息：
SET LINESIZE 1200
COL NAME FOR A60
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的介绍如下
|
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 |
SET LINESIZE 1200
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
,FILE_NAME AS FILE_NAME
,BLOCKS AS BLOCKS
,STATUS AS STATUS
,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS "MAX_SIZE(G)"
,INCREMENT_BY AS "INCREMENT_BY"
,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;
DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.
|
Column |
Datatype |
NULL |
Description |
|
FILE_NAME |
VARCHAR2(513) |
Name of the database temp file |
|
|
FILE_ID |
NUMBER |
File identifier number of the database temp file |
|
|
TABLESPACE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the tablespace to which the file belongs |
|
BYTES |
NUMBER |
Size of the file (in bytes) |
|
|
BLOCKS |
NUMBER |
Size of the file (in Oracle blocks) |
|
|
STATUS |
CHAR(9) |
File status: · · AVAILABLE |
|
|
RELATIVE_FNO |
NUMBER |
Tablespace-relative file number |
|
|
AUTOEXTENSIBLE |
VARCHAR2(3) |
Indicates whether the file is autoextensible (YES) or not (NO) |
|
|
MAXBYTES |
NUMBER |
maximum size of the file (in bytes) |
|
|
MAXBLOCKS |
NUMBER |
Maximum size of the file (in Oracle blocks) |
|
|
INCREMENT_BY |
NUMBER |
Default increment for autoextension |
|
|
USER_BYTES |
NUMBER |
Size of the useful portion of the file (in bytes) |
|
|
USER_BLOCKS |
NUMBER |
Size of the useful portion of the file (in Oracle blocks) |
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，如下图所示:
管理临时表空间
创建临时表空间
下面是一个简单的创建临时表空间的例子，具体很多细节可以参考官方文档，这里省略,不做过多介绍。
http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366
CREATE TEMPORARY TABLESPACE TMP
TEMPFILE '/u01/gsp/oradata/TMP01.dbf'
SIZE 8G
AUTOEXTEND OFF;
增加数据文件
当临时表空间太小时，就需要扩展临时表空间（添加数据文件、增大数据文件、设置文件自动扩展）；有时候需要将临时数据文件分布到不同的磁盘分区中，提升IO性能，也需要通过删除、增加临时表空间数据文件。
SQL> ALTER TABLESPACE TEMP
2 ADD TEMPFILE '/u04/gsp/oradata/temp02.dbf'
3 SIZE 4G
4 AUTOEXTEND ON
5 NEXT 128M
6 MAXSIZE 6G;
Tablespace altered.
SQL> ALTER TABLESPACE TMP
ADD TEMPFILE '/u03/eps/oradata/temp02.dbf'
SIZE 64G
AUTOEXTEND OFF;
Tablespace altered.
删除数据文件
例如，我想删除临时表空间下的某个文件，那么我们有两种方式删除临时表空间的数据文件。
方法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.
注意：删除临时表空间的临时数据文件时，不需要指定INCLUDING DATAFILES 选项也会真正删除物理文件，否则需要手工删除物理文件。
调整文件大小
如下例子，需要将临时数据文件从1G大小调整为2G
SQL> ALTER DATABASE TEMPFILE
'/u01/app/oracle/oradata/GSP/temp02.dbf' RESIZE 2G;
文件脱机联机
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp02.dbf' OFFLINE;
Database altered.
SQL> ALTER DATABASE TEMPFILE
2 '/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
设置文件自动扩展
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp03.dbf'
2 AUTOEXTEND ON
3 NEXT 100M
4 MAXSIZE UNLIMITED;
移动重命名文件
例如，我需要将/u01/app/oracle/oradata/GSP/temp4.dbf这个文件重命名为/u01/app/oracle/oradata/GSP/temp04.dbf
1: 将临时表空间的临时文件脱机
SQL> ALTER DATABASE TEMPFILE
2 '/u01/app/oracle/oradata/GSP/temp4.dbf' OFFLINE;
2：移动或重命名相关的临时文件
mv /u01/app/oracle/oradata/GSP/temp4.dbf /u01/app/oracle/oradata/GSP/temp04.dbf'
3: 使用脚本ALTER DATABASE RENAME FILE
SQL> ALTER DATABASE RENAME FILE
2 '/u01/app/oracle/oradata/GSP/temp4.dbf' TO
3 '/u01/app/oracle/oradata/GSP/temp04.dbf';
4: 将临时表空间的临时文件联机
SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/GSP/temp04.dbf' ONLINE;
Database altered.
删除临时表空间
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
注意：不能删除当前用户的默认表空间，否则会报ORA-12906错误
SQL> DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
如果需要删除某一个默认的临时表空间，则必须先创建一个临时表空间，然后指定新创建的表空间为默认表空间，然后删除原来的临时表空间
临时表空间组
临进表空间组:
临进表空间组是ORACLE 10g引入的一个新特性，它是一个逻辑概念，不需要显示的创建和删除。只要把一个临时表空间分配到一个组中，临时表空间组就自动创建，所有的临时表空间从临时表空间组中移除就自动删除。
一个临时表空间组必须由至少一个临时表空间组成，并且无明确的最大数量限制.
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.
可以在创建临时表空间是指定表空间组，即隐式创建。
SQL>CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE '/u01/app/oracle/oradata/GSP/temp2_1.dbf' SIZE 200M
TABLESPACE GROUP GRP_TEMP;
查看临时表空间组：
SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP TEMP2
也可以指定已经创建好的临时表空间的临时表空间组。
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP GRP_TEMP;
Tablespace altered.
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TEMP TEMP
GRP_TEMP TEMP2
从组中移除：
SQL> ALTER TABLESPACE TEMP TABLESPACE GROUP '';
当为数据库指定临时表空间或为用户指定临时表空间时，可以使用临时表空间组的名称
ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP;
切换临时表空间
1：查看旧临时表空间信息
SELECT * FROM V$TEMPFILE
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS
2：创建中转的临时表空间
3：添加相应的数据文件
4：切换临时表空间。
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;
5：删除旧的临时表空间数据文件
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
6：如果有必要，重新指定用户临时表空间为新建的临时表空间
ALTER USER ODS TEMPORARY TABLESPACE TMP;
ALTER USER EDS TEMPORARY TABLESPACE TMP;
ALTER USER ETL TEMPORARY TABLESPACE TMP;
ALTER USER DM TEMPORARY TABLESPACE TMP;
收缩临时表空间
排序等操作使用的临时段，使用完成后会被标记为空闲，表示可以重用，占用的空间不会立即释放，有时候临时表空间会变得非常大，此时可以通过收缩临时表空间来释放没有使用的空间。收缩临时表空间是ORACLE 11g新增的功能。
SQL> ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G;
SQL> ALTER TABLESPACE TEMP SHRINK TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf'
监控临时表空间
查看临时表空间使用情况：
SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",
TT.TOTAL - TU.USED AS "FREE(G)",
TT.TOTAL AS "TOTAL(G)",
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;
COL TEMP_FILE FOR A60;
SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",
D.FILE_NAME AS "TEMP_FILE",
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)" ,
ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2) AS "TOTAL(GB)",
ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,
ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2) AS "USED(GB)"
FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND P.FILE_ID(+) = D.FILE_ID;
查看临时表空间对应的临时文件的使用情况
SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,
BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED ,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
查找消耗临时表空间资源比较多的SQL语句
SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
WHERE p.name = 'db_block_size'
AND su.session_addr = se.saddr
AND s.hash_value = su.sqlhash
AND s.address = su.sqladdr
ORDER BY se.username, se.sid;
参考资料：
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11359
