今早巡检数据库发现如下报错：
Fri Jul 15 06:03:03 2011
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
……
Fri Jul 15 08:02:59 2011
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
通过查看表空间发现，TEMP表空间被扩展到了32G，而原先设置的只有4G，我们通过如下语句分析TEMP表空间有什么东西这么耗空间：
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
Order By machine
查询结果如下：
| USERNAME
|
SID
|
SERIAL#
|
SQL_ADDRESS
|
MACHINE
|
PROGRAM
|
TABLESPACE
|
SEGTYPE
|
CONTENTS
|
| PHC |
831 |
307 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
897 |
10837 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
928 |
62946 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
893 |
59116 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
1081 |
30775 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
1025 |
24989 |
00 |
PORT-web |
|
TEMP |
LOB_DATA |
TEMPORARY |
| PHC |
907 |
10378 |
00 |
WORKGROUP\PORT-SJJH2 |
PLSQLDev.exe |
TEMP |
LOB_DATA |
TEMPORARY |
| EPORT_ADMIN |
1080 |
63733 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
923 |
43188 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
1047 |
23 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
929 |
29713 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
816 |
44578 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
910 |
20498 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
969 |
50313 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
915 |
57 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
876 |
7930 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
906 |
40178 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
836 |
26868 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
| EPORT_ADMIN |
912 |
50385 |
000000025B61AF28 |
WORKGROUP\TEST-WZTJFZ |
logread.exe |
TEMP |
SORT |
TEMPORARY |
仔细看如下查询结果，EPORT_ADMIN是以前很少见到的，怀疑logread.exe存在嫌疑，考虑到这只是个数据同步进程，考虑如下命令KILL掉：
Alter system kill session '1080,63733';
Alter system kill session '923,43188';
Alter system kill session '1047,23';
Alter system kill session '929,29713';
Alter system kill session '816,44578';
Alter system kill session '910,20498';
Alter system kill session '969,50313';
Alter system kill session '915,57';
Alter system kill session '876,7930';
Alter system kill session '906,40178';
Alter system kill session '836,26868';
Alter system kill session '912,50385';
语句执行后EPORT_ADMIN的进程在TEMP表空间消失，再来看TEMP表空间，已经基本清空：
| Name |
Size (MB) |
Used (MB) |
Used (%) |
Free (MB) |
Status |
Datafiles |
Type |
| 32,767.00 |
47 |
32,720.00 |
|
1 |
TEMPORARY |
结论：显然EPORT_ADMIN下的logread.exe存在大量表空间的开销，这在业务高峰期是非常危险的现象。
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/611735，如需转载请自行联系原作者
