今早巡检数据库发现如下报错:
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存在大量表空间的开销,这在业务高峰期是非常危险的现象。