转载地址：http://www.chinaunix.net/old_jh/19/272020.html

临时表空间作用

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。

网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：

索引create或rebuild

Order by 或 group by

Distinct 操作

Union 或 intersect 或 minus

Sort-merge joins

analyze

正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段a的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。这个问题在论坛中也常被网友问到，下面我总结一下，给出几种处理方法。



法一、重启库



库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。



法二、Metalink给出的一个方法



修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。



SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;



法三、我常用的一个方法,具体内容如下：



1、使用如下语句a查看一下认谁在用临时段



SELECT username,
       sid,
       serial#,
       sql_address,
       machine,
       program,
       tablespace,
       segtype,
       contents
  FROM v$session se,
       v$sort_usage su
WHERE se.saddr=su.session_addr



2、   那些正在使用临时段的进程

SQL>;Alter system kill session 'sid,serial#';



3、把TEMP表空间回缩一下

SQL>;Alter tablespace TEMP coalesce;



法四、使用诊断事件的一种方法,也是被我认为是“杀手锏”的一种方法



1、确定TEMP表空间的ts#



SQL>select ts#, name from sys.ts$ ;

TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
4 TOOLS
5 INDX
6 DRSYS

2、执行清理操作

SQL>;alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;

说明：
temp表空间的TS# 为 3*, So TS#+ 1= 4



其它：



1、出现如上问题的原因我认为可能是由于大的排序超出了TEMP表空间的空间允许范围引起的。也可能包含着其它的异常的因素。



2、观注TEMP等这些空间的状态是Dba日常职责之一，我们可以通过Toad、Object Browser等这些工具办到，也可以用如下的语句：


--MB为单位
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比",
       F.TOTAL_BYTES "空闲空间(M)",
       F.MAX_BYTES "最大块(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC

--GB为单位
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_GB "表空间大小(G)",
D.TOT_GROOTTE_GB - F.TOTAL_BYTES "已使用空间(G)",
TO_CHAR(ROUND((D.TOT_GROOTTE_GB - F.TOTAL_BYTES) / D.TOT_GROOTTE_GB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(G)",
F.MAX_BYTES "最大块(G)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_GB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC

转载地址：http://database.51cto.com/art/201107/273018_all.htm

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。

排序是很耗资源的，Temp表空间满了,关键是优化你的语句，尽量使排序减少才是上策.

Temp表空间满时的处理方法：

一、修改参数(仅适用于8i及8i以下版本)

修改一下TEMP表空间的storage参数，让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。


	
	
		SQL>alter tablespace temp increase 1;   
	

	
		  
	

	
		SQL>alter tablespace temp increase 0;

二、kill session

1、使用如下语句a查看一下认谁在用临时段


	
	
		SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine, se.program, su.TABLESPACE,   
	

	
		  
	

	
		su.segtype,  su.CONTENTS FROM v$session se, v$sort_usage su   
	

	
		  
	

	
		WHERE se.saddr = su.session_addr

2、kill正在使用临时段的进程


	
	
		SQL>Alter system kill session 'sid,serial#';

3、把TEMP表空间回缩一下


	
	
		SQL>Alter tablespace TEMP coalesce;

注：

这处方法只能针对字典管理表空间(Dictionary Managed Tablespace)。于本地管理表空间（LMT:Local Managed Tablespace），不需要整理的。9i以后只能创建本地管理的表空间。


	
	
		CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT DICTIONARY   
	

	
		  
	

	
		CREATE TABLESPACE TEST DATAFILE 'D:\TEST01.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL;

三、重启数据库库

库重启时，Smon进程会完成临时段释放,TEMP表空间的清理操作，不过很多的时侯我们的库是不允许down的,所以这种方法缺少了一点的应用机会，不过这种方法还是很好用的。

四、使用诊断事件的一种方法,也是最有效的一种方法

1、确定TEMP表空间的ts#


	
	
		SQL>select ts#, name from sys.ts$ ;   
	

	
		  
	

	
		TS# NAME   
	

	
		  
	

	
		---------- ------------------------------   
	

	
		  
	

	
		0 SYSTEM   
	

	
		  
	

	
		1 UNDOTBS1   
	

	
		  
	

	
		2 SYSAUX   
	

	
		  
	

	
		3 TEMP   
	

	
		  
	

	
		4 USERS   
	

	
		  
	

	
		5 UNDOTBS2

2、执行清理操作


	
	
		SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;

说明：

temp表空间的TS# 为 3, So TS#+ 1= 4

重建TEMP 表空间：

Temporary tablespace是不能直接drop默认的临时表空间的，不过我们可以通过以下方法来做。

准备：查看目前的Temporary Tablespace


	
	
		SQL> select name from v$tempfile;   
	

	
		  
	

	
		NAME   
	

	
		  
	

	
		———————————————————————   
	

	
		  
	

	
		D:\ORACLE\ORADATA\TEST\TEMP01.DBF   
	

	
		  
	

	
		SQL> select username,temporary_tablespace from dba_users;   
	

	
		  
	

	
		USERNAME                    TEMPORARY_TABLESPACE   
	

	
		  
	

	
		------------------------------ ------------------------------   
	

	
		  
	

	
		MGMT_VIEW                  TEMP   
	

	
		  
	

	
		SYS                                TEMP   
	

	
		  
	

	
		SYSTEM                         TEMP   
	

	
		  
	

	
		DBSNMP                         TEMP   
	

	
		  
	

	
		SYSMAN                         TEMP

1.创建中转临时表空间


	
	
		create temporary tablespace  TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;

2.改变缺省临时表空间为刚刚创建的新临时表空间temp1


	
	
		alter database default  temporary tablespace  temp1;

3.删除原来临时表空间


	
	
		drop tablespace temp including contents and datafiles;

4.重新创建临时表空间


	
	
		create temporary tablespace  TEMP TEMPFILE 'E:\ORACLE\ORADATA\ORCL\temp01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT  1M MAXSIZE UNLIMITED;

5.重置缺省临时表空间为新建的temp表空间


	
	
		alter database default  temporary tablespace  temp;

6.删除中转用临时表空间


	
	
		drop tablespace temp1 including contents and datafiles;

7.如果有必要，那么重新指定用户表空间为重建的临时表空间


	
	
		alter user arbor temporary tablespace  temp;

查看表空间语句，不过查不出Temp表空间：


	
	
		SELECT UPPER(F.TABLESPACE_NAME) "表空间名",   
	

	
		  
	

	
		D.TOT_GROOTTE_MB "表空间大小(M)",   
	

	
		  
	

	
		D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",   
	

	
		  
	

	
		TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),   
	

	
		  
	

	
		'990.99') "使用比",   
	

	
		  
	

	
		F.TOTAL_BYTES "空闲空间(M)",   
	

	
		  
	

	
		F.MAX_BYTES "最大块(M)"   
	

	
		  
	

	
		FROM (SELECT TABLESPACE_NAME,   
	

	
		  
	

	
		ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,   
	

	
		  
	

	
		ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES   
	

	
		  
	

	
		FROM SYS.DBA_FREE_SPACE   
	

	
		  
	

	
		GROUP BY TABLESPACE_NAME) F,   
	

	
		  
	

	
		(SELECT DD.TABLESPACE_NAME,   
	

	
		  
	

	
		ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB   
	

	
		  
	

	
		FROM SYS.DBA_DATA_FILES DD   
	

	
		  
	

	
		GROUP BY DD.TABLESPACE_NAME) D   
	

	
		  
	

	
		WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME   
	

	
		  
	

	
		ORDER BY 4 DESC

1、查看临时表空间大小：

select name,to_char(bytes/1024/1024)||'M' from v$tempfile;

结果显示临时表大小为0M，如下显示：

NAME TO_CHAR(BYTES/1024/1024)||'M'

/app/oracle/oradata/metenoa/temp01.dbf 0M

2、然后再看了一下数据库文件的挂载状态：

select file#,name,status,enabled from v$tempfile;

显示STATUS为offline

利用以下语句进行处理：

alter database tempfile '/app/oracle/oradata/metenoa/temp01.dbf' online;

然后再查看一下临时表状态，如下显示(ONLINE正常啦)：

FILE# NAME STATUS ENABLED

1 /app/oracle/oradata/metenoa/temp01.dbf ONLINE READ WRITE

再查看一下空间大小，有数值显示了,如下：

NAME TO_CHAR(BYTES/1024/1024)||'M'

/app/oracle/oradata/metenoa/temp01.dbf 351M

转载地址：http://www.ifudon.com/post/2012-10-20/temp_tablespace.html

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小…

临时表空间的主要作用：
---- 索引create或rebuild；
---- Order by 或 group by；
---- Distinct 操作；
---- Union 或 intersect 或 minus；
---- Sort-merge joins；
---- analyze。

1、查看临时表空间文件信息（dba_temp_files视图、v_$tempfile视图）

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;
2、缩小临时表空间大小（不释放临时表空间）

alter database tempfile '+DGDATA/racdb/datafile/dwh_temp1.dbf' resize 500M;
注：通过resize表空间datafile重定义表空间大小，但新文件大小不能小于已经存储数据的实际大小。

3、扩展临时表空间

--方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;
--方法二、将临时数据文件设为自动扩展：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
--方法三、向临时表空间中添加数据文件：
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;
4、创建临时表空间

SQL> create temporary tablespace tbs_tmp tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ size 10M;
5、更改系统的默认临时表空间

--查询系统默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认临时表空间
--所有使用系统默认临时表空间的用户都将切换为新的临时表空间
alter database default temporary tablespace tbs_tmp;
select username,temporary_tablespace from dba_users;
--更改某一用户的临时表空间
alter user scott temporary tablespace temp;
6、删除临时表空间

--删除临时表空间的一个数据文件：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;
--删除临时表空间(彻底删除)：
SQL> drop tablespace tbs_tmp including contents and datafiles cascade constraints;
7、查看临时表空间的使用情况（GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询）

GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小，DBA_TEMP_FILES视图的bytes字段记录的是临时表空间的总大小。

SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM GV_$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;
8、查找消耗资源比较的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;
9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句

select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;
10、临时表空间组介绍

1）创建临时表空间组：

create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1;
create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2;
2）查询临时表空间组：dba_tablespace_groups视图

select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------------------- ------------------------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2
3）将表空间从一个临时表空间组移动到另外一个临时表空间组：

alter tablespace tempts1 tablespace group GROUP2;
select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
--------------------------- ------------------------------
GROUP2 TEMPTS1
GROUP2 TEMPTS2
4）把临时表空间组指定给用户

SQL> alter user scott temporary tablespace GROUP2;
5）在数据库级设置临时表空间

SQL> alter database <db_name> default temporary tablespace GROUP2;
6）删除临时表空间组 (删除组成临时表空间组的所有临时表空间)

drop tablespace tempts1 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------------------- ------------------------------
GROUP2 TEMPTS2

drop tablespace tempts2 including contents and datafiles;
select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
--------------------------- ------------------------------
11、对临时表空间进行shrink（11g新增的功能，实现和上面第2点相同的功能）

--将temp表空间收缩为20M
alter tablespace temp shrink space keep 20M;
--自动将表空间的临时文件缩小到最小可能的大小
ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;
12、释放临时表空间的方法

1、重新启动数据库

2、新建临时表空间，切换到新的临时表空间，删除原临时表空间

ITPub博客

临时表空间不释放

临时表空间作用