<span style="font-family:Verdana;">
1、查看当前临时表空间的情况
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> @temp_sort_segment

+==================================================================================+
| Segment Name : The segment name is a concatenation of the |
| SEGMENT_FILE (File number of the first extent) |
| and the |
| SEGMENT_BLOCK (Block number of the first extent) |
| Current Users : Number of active users of the segment |
| Total Temp Segment Size : Total size of the temporary segment in MB |
| Currently Used Bytes : Bytes allocated to active sorts |
| Extent Hits : Number of times an unused extent was found in the pool |
| Max Size : Maximum number of MB ever used |
| Max Used Size : Maximum number of MB used by all sorts |
| Max Sort Size : Maximum number of MB used by an individual sort |
| Free Requests : Number of requests to deallocate |
+==================================================================================+
-->此时临时表空间go_temp中达到了32GB
Tablespace Segment Current Currently Pct. Extent Max Max Used Max Sort Free
Name Name Users Used MB Used Hits Size MB Size MB Size MB Requests
-------------- -------- ------- --------- ---- -------- -------- -------- -------- --------
TEMP SYS.0.0 4 4 2 1,864 217 217 217 0
GO_TEMP SYS.0.0 0 0 0 1,305 32,766 367 367 0
************** ------- --------- -------- -------- -------- -------- --------
sum 4 4 3,169 32,983 584 584 0

SQL> col tbsname format a15
SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status
2 from v$tablespace s,v$tempfile t
3 where s.ts# = t.ts#;

TBSNAME NAME MB STATUS
--------------- -------------------------------------------------- ---------- -------
TEMP /u02/database/ORADB/temp/tempORADB.dbf 235 ONLINE
GO_TEMP /u02/database/ORADB/temp/ORADB_tempORADB.dbf 32767 ONLINE

SQL> @temp_usage2 -->此时temp已使用的为4MB，而GO_TEMP未使用

TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
GO_TEMP 32767 0 32767
TEMP 218 4 214

2、观察及分析临时表空间的耗用情况
SQL> select count(*) from big_table; -->开启另一个session

COUNT(*)
----------
2000000

SQL> select * from big_table order by 2,3,4,5,7,8 desc; -->对big_table 实施排序

SQL> alter index pk_stock_tbl_arc rebuild; -->开启另一个session重建索引

SQL> @temp_sort_segment.sql -->可以看到此时temp表空间耗用达到234MB,go_temp的耗用达到375MB

Tablespace Segment Current Currently Pct. Extent Max Max Used Max Sort Free
Name Name Users Used MB Used Hits Size MB Size MB Size MB Requests
-------------- -------- ------- --------- ---- -------- -------- -------- -------- --------
TEMP SYS.0.0 4 234 2 2,077 234 234 230 0
GO_TEMP SYS.0.0 1 375 1 2,055 32,766 375 375 0
************** ------- --------- -------- -------- -------- -------- --------
sum 5 609 4,132 33,000 609 605 0

SQL> @temp_sort_users.sql -->获得当前排序的会话

INST_ID SID_SERIAL Username OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS
---------- ---------- ---------- --------------- ------------ ---------- ---------- ---------- ---------- ----------
1 1064,9259 SCOTT oracle 14456 SQL*Plus oracle@SZD 234 TEMP 4
B (TNS V1-
V3)

1 1073,5166 GO_ADMIN oracle 2480 SQL*Plus oracle@SZD 375 GO_TEMP 1
B (TNS V1-
V3)
3、使用resize，缩小临时表空间，如不能缩小，转到下一步
SQL> SELECT 'alter database tempfile ''' || a.name || ''' resize ' || b.siz || 'M;' resize_command
2 FROM v$tempfile a
3 ,(SELECT ceil(tmsize.maxblk * bk.value / 1024 / 1024) siz
4 FROM (SELECT nvl(MAX(segblk#), 128) maxblk
5 FROM v$sort_usage) tmsize
6 ,(SELECT VALUE
7 FROM v$parameter
8 WHERE NAME = 'db_block_size') bk) b;

RESIZE_COMMAND
----------------------------------------------------------------------------------------
alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;
alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;

-->实际上此时占用32GB的临时数据文件已经缩小
alter database tempfile '/u02/database/ORADB/temp/ORADB_tempORADB.dbf' resize 106M;

Database altered.

-->为便于演示，此时假定TEMP为过大的临时表空间且不能释放
-->下面调整表明已使用空间超出了分配的空间
SQL> alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M;
alter database tempfile '/u02/database/ORADB/temp/tempORADB.dbf' resize 106M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> select count(*) from v$sort_usage where tablespace='TEMP'; -->当前有未释放的临时段

COUNT(*)
----------
4

/**************************************************/
/* Author: Robinson Cheng */
/* Blog: http://blog.csdn.net/robinson_0612 */
/* MSN: robinson_0612@hotmail.com */
/* QQ: 645746311 */
/**************************************************/

4、新建一个中转临时表空间
SQL> create temporary tablespace temp2 tempfile '/u02/database/ORADB/temp/ORADB_temp02.dbf'
2 size 10m autoextend on;

Tablespace created.

-->如果此时过大的临时表空间为缺省的临时表空间，则必须将缺省的临时表空间设置为新的临时表空间之后
SQL> select property_name,property_value from database_properties
2 where property_name like 'DEFAULT_TEMP_TABLESPACE';

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

SQL> alter database default temporary tablespace temp2;

Database altered.

5、转移用户到中转临时表空间
-->过大临时表空间上的那些用户需要迁移到新建的临时表空间
-->查询DBA_users视图查询哪些用户位于过大的临时表空间之上
-->并使用下面的命令将其切换到新的临时表空间
alter user <username> temporary tablespace temp2;

6.等到过大临时表空间上的没有临时段被使用，即已经全部释放即可删除过大的临时表空间

SQL> show user; -->由于当前用户为scott，所以临时表空间未能释放
USER is "SCOTT"

SQL> conn / as sysdba -->切换到sysdba
Connected.

SQL> @temp_usage2 -->临时段已经被释放

TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
GO_TEMP 106 0 106
TEMP 235 0 235

-->如果没有释放在可以kill session的情况下kill session.利用前面获得的sid,serial#来执行(前提是允许该情况发生).
alter system kill session '1064,9259'

7.删除过大的临时表空间

SQL> alter tablespace temp tempfile offline; -->先将其脱机

Tablespace altered.

SQL> drop tablespace temp including contents and datafiles; -->删除临时表空间及相应的文件

Tablespace dropped.

SQL> select s.name tbsname,t.name,(t.bytes/1024/1024) mb,t.status
2 from v$tablespace s,v$tempfile t
3 where s.ts# = t.ts#;

TBSNAME NAME MB STATUS
--------------- -------------------------------------------------- ---------- -------
GO_TEMP /u02/database/ORADB/temp/ORADB_tempORADB.dbf 106 ONLINE
TEMP2 /u02/database/ORADB/temp/ORADB_temp02.dbf 10 ONLINE

-->也可以使用下面的命令来完成仅仅删除单个文件
ALTER DATABASE TEMPFILE '/u02/database/ORADB/temp/tempORADB.dbf' DROP INCLUDING DATAFILES; -->删除单个文件

7、根据需求可以创建原来的临时表空间并将切换出去用户切换到此临时表空间</span>