修改TEMP表空间的storage参数
这个方法来自Metalink主要是让Smon进程观注一下临时段，从而达到清理和TEMP表空间的目的。
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
以上方法在9I和10G不行，
SQL> alter tablespace temp PCTINCREASE 1;
alter tablespace temp PCTINCREASE 1
*
第 1 行出现错误:
ORA-02142: ALTER TABLESPACE 选项缺失或无效
杀死会话，回收收缩
句a查看一下认谁在用临时段
SQL>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
杀死正在使用临时段的进程
SQL>Alter system kill session 'sid,serial#';
回缩TEMP表空间
SQL>Alter tablespace TEMP coalesce;
使用诊断事件
确定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
执行清理操作
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4' ;
说明：
temp表空间的TS# 为 3*, So TS#+ 1= 4
一下是摘自Metalink的说明
The DROP_SEGMENTS event
~~~~~~~~~~~~~~~~~~~~~~~
Available from 8.0 onwards.
DESCRIPTION
Finds all the temporary segments in a tablespace which are not
currently locked and drops them.
For the purpose of this event a "temp" segment is defined as a
segment (seg$ entry) with TYPE#=3. Sort space in a TEMPORARY
tablespace does not qualify under this definition as such
space is managed independently of SEG$ entries.
PARAMETERS
level - tablespace number+1. If the value is 2147483647 then
temp segments in ALL tablespaces are dropped, otherwise, only
segments in a tablespace whose number is equal to the LEVEL
specification are dropped.
NOTES
This routine does what SMON does in the background, i.e. drops
temporary segments. It is provided as a manual intervention tool which
the user may invoke if SMON misses the post and does not get to
clean the temp segments for another 2 hours. We do not know whether
missed post is a real possibility or more theoretical situation, so
we provide this event as an insurance against SMON misbehaviour.
Under normal operation there is no need to use this event.
It may be a good idea to
alter tablespace
after dropping lots of extents to tidy things up.
*SQL Session (if you can connect to the database):
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
The TS# can be obtained from v$tablespace view:
select ts# from v$tablespace where name = '
Or from SYS.TS$:
select ts# from sys.ts$ where name = '
If ts# is 5, an example of dropping the temporary segments in that tablespace
would be:
alter session set events 'immediate trace name DROP_SEGMENTS level 6';
查看临时表空间的使用情况（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
查找比较消耗临时表空间资源的sql语句
Select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,