oracle处理temp表空间爆长的问题(二)
------ ------------- ------------ ----------- -----------
TEMP 1 13696 13696 0
SQL> select username,session_addr,sqladdr,sqlhash,tablespace from v$sort_usage;
USERNAME SESSION_ SQLADDR SQLHASH TABLESPACE
---------- -------- -------- ---------- --------------------
SYS 682102D8 66BE52D8 919180126 TEMP
更多的信息如下：
col tablespace format a20
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;
USERNAME SID SERIAL# EXTENTS SPACE TABLESPACE SEGTYPE
---------- ---------- ---------- ---------- ---------- -------------------- ---------
SQL_TEXT
----------------------------------------------------------------
SYS 9 5 107 112197632 TEMP SORT
select * from arbor.server_lookup where rownum<1000000 order by
bill_lname_find
一般来说，排序操作都很快，由于本例是测试，所以temp表空间已经撑爆了，结果还没有出来，所以可以抓到相应的sql。
而这时候，在另一个会话中，我的查询语句已经结束了，但是没有返回任何结果！
metalink推荐下列一些方法：
--重启实例
重启实例重启时，smon进程对临时段释放,但是对于生产库，我们就不能用这种方法了。。。。
如果重启后sort段没有被释放呢？
--修改参数(仅适用于8i及8i以下版本)
SQL>alter tablespace temp increase 1;
SQL>alter tablespace temp increase 0;
--合并碎片（仅适用于字典管理的表空间）
1、首先看谁在用临时段
SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr;
USERNAME SID SERIAL# SQL_ADDR MACHINE PROGRAM TABLESPACE
---------- ---------- ---------- -------- -------------------- ---------------------------------------- ----------
SYS 9 3 66CDF6D0 ASPIRE\GZ-ZHAOLEHUAN sqlplus.exe TEMP
2、然后将那些会话Kill掉
Alter system kill session 'sid,serial#';
SQL> alter system kill session '9,3';
系统已更改。
3、整理下表空间
Alter tablespace TEMP coalesce
*
ERROR 位于第 1 行:
ORA-03217: 变更 TEMPORARY TABLESPACE 无效的选项
而事实上，这处方法只能针对字典管理的表空间。对于本地管理的表空间，不是需要整理的。
--诊断事件
select ts#, name from sys.ts$ ;
TS# NAME
---------- --------------------
0 SYSTEM
1 UNDOTBS1
2 TEMP
3 CWMLITE
4 DRSYS
5 EXAMPLE
6 INDX
7 ODM
8 TOOLS
9 USERS
10 XDB
11 CAT_CUST
12 CAT_CUST_IDX
13 zlh_test
14 ZLH_TEST
15 TEMP1
已选择16行。
SQL>alter session set events 'immediate trace name DROP_SEGMENTS level 4'
说明：level=TS#+ 1
--重建temp
1.创建中转临时表空间
create temporary tablespace TEMP1 TEMPFILE 'E:\ORACLE\ORADATA\ORCL9\temp02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
2.改变缺省临