temp表空间占用过大，释放temp临时表空间
临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理。临时表空间消耗的主要原因是需要对查询的中间结果进行排序，当oracle里需要用到sort的时候,PGA中sort\_area\_size大小不够时,将会把数据放入临时表空间里进行排序,同时如果有异常情况的话,也会被放入临时表空间,正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临时段的。但有时会遇到临时段没有被释放,TEMP表空间持续增长到耗尽磁盘空间。当数据库实例重启后，临时表空间一般就会释放掉。需要注意的是，当前临时表空间文件的大小是历史上使用临时表空间最大的大小，并不代表现在实际使用的大小。当临时表空间撑满后，就会影响到系统性能，这时我们就需要人为干涉来适当释放表空间大小。
方法1 临时表空间 shrink
1、临时表空间查看
查询临时表空间使用情况
SELECT df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl( FS.UsedSpace, 0 ) "Used(MB)", ( df.totalspace - nvl( FS.UsedSpace, 0 ) ) "Free(MB)", round( 100 * ( 1- ( nvl( fs.UsedSpace, 0 ) / df.totalspace ) ), 2 ) "Pct. Free(%)" FROM ( SELECT tablespace_name, round( SUM( bytes ) / 1048576 ) TotalSpace FROM dba_TEMP_files GROUP BY tablespace_name ) df, ( SELECT tablespace_name, ROUND( SUM( bytes_used ) / 1024 / 1024 ) UsedSpace FROM gV$temp_extent_pool GROUP BY tablespace_name ) fs WHERE df.tablespace_name = fs.tablespace_name ( + );例：
sys@ORCL 08:45:46 SQL> select df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl(FS.UsedSpace, 0) "Used(MB)", 2 3 4 (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)", round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)" FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace 7 FROM dba_TEMP_files 8 GROUP BY tablespace_name) df, (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace 10 FROM gV$temp_extent_pool 11 GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+); Tablespace Total(MB) Used(MB) Free(MB) Pct. Free(%) ------------------------------ ---------- ---------- ---------- ------------ TEMP 7 0 7 100
2、收缩
alter tablespace temp shrink space;方法2 重建临时表空间
1、临时表空间查看
查询临时表空间使用情况
SELECT df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl( FS.UsedSpace, 0 ) "Used(MB)", ( df.totalspace - nvl( FS.UsedSpace, 0 ) ) "Free(MB)", round( 100 * ( 1- ( nvl( fs.UsedSpace, 0 ) / df.totalspace ) ), 2 ) "Pct. Free(%)" FROM ( SELECT tablespace_name, round( SUM( bytes ) / 1048576 ) TotalSpace FROM dba_TEMP_files GROUP BY tablespace_name ) df, ( SELECT tablespace_name, ROUND( SUM( bytes_used ) / 1024 / 1024 ) UsedSpace FROM gV$temp_extent_pool GROUP BY tablespace_name ) fs WHERE df.tablespace_name = fs.tablespace_name ( + );例：
sys@ORCL 08:45:46 SQL> select df.tablespace_name "Tablespace", df.totalspace "Total(MB)", nvl(FS.UsedSpace, 0) "Used(MB)", 2 3 4 (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)", round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)" FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace 7 FROM dba_TEMP_files 8 GROUP BY tablespace_name) df, (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace 10 FROM gV$temp_extent_pool 11 GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+); Tablespace Total(MB) Used(MB) Free(MB) Pct. Free(%) ------------------------------ ---------- ---------- ---------- ------------ TEMP 7 0 7 100查询默认临时表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';查看现有temp临时表的名字及路径
select name from v$tempfile;sys@ORCL 08:47:50 SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DATA/ORCL/TEMPFILE/temp.286.1143120237 +DATA/ORCL/0149F75FB901A530E0638300640A99E7/TEMPFILE/temp.279.1143120383 +DATA/ORCL/014A21717FCFCA0DE0638300640ACB95/TEMPFILE/temp.264.1143121067查询哪些用户使用临时表空间
select username,temporary_tablespace from dba_users;
2、创建临时中转表空间
create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/tempfile/temp01.dbf' size 512M reuse autoextend on next 1M maxsize 2G;3、更改默认临时表空间为临时中转表空间
alter database default temporary tablespace temp01;4、查确认默认表空间是否为新创建的临时中转表空间
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';5、查询出正在使用temp表空间的会话进程并删除掉
SELECT
se.username,
se.sid,
se.serial#,
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;kill会话
alter system kill session 'sid,serial#';6、删除原来临时表空间
drop tablespace temp including contents and datafiles;7、重新创建temp临时表空间
create temporary tablespace temp tempfile '/u01/app/oracle/oradata/tempfile/temp.dbf' size 512M reuse autoextend on next 100M maxsize 3g;8、重置默认临时表空间为temp
alter database default temporary tablespace temp;9、删除中转用临时表空间
drop tablespace temp01 including contents and datafiles;10、如果有必要，重新指定用户表空间为重建的临时表空间
alter user test temporary tablespace temp; 查询temp表空间使用率：
select df.tablespace_name "Tablespace",
df.totalspace "Total(MB)",
nvl(FS.UsedSpace, 0) "Used(MB)",
(df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",
round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_TEMP_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024) UsedSpace
FROM gV$temp_extent_pool
GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name(+);查询实时使用temp表空间的sql\_id和sid：
set linesize 260 pagesize 1000
col machine for a40
col program for a40
SELECT se.username,
sid,
serial#,
se.sql_id
machine,
program,
tablespace,
segtype,
(su.BLOCKS*8/1024/1024) GB
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr
order by su.BLOCKS desc;
/*需要注意的是这里查询sql_id要用v$session视图的sql_id，而不要用v$sort_usage视图的sql_id，v$sort_usage视图里面的sql_id是不准确的*/查询历史的temp表空间的使用的SQL\_ID:
select a.SQL_ID,
a.SAMPLE_TIME,
a.program,
sum(trunc(a.TEMP_SPACE_ALLOCATED / 1024 / 1024)) MB
from v$active_session_history a
where TEMP_SPACE_ALLOCATED is not null
and sample_time between
to_date('&date1', 'yyyy-mm-dd hh24:mi:ss') and
to_date('&date2', 'yyyy-mm-dd hh24:mi:ss')
group by a.sql_id,a.SAMPLE_TIME,a.PROGRAM
order by 2 asc,4 desc;