如何捕捉temp表空間出錯的session資訊和SQL

我們有時候會遇見這樣的煩惱，在特定的時候總有某個大查詢導致臨時表空間出錯。但我們總不能守侯著捕獲相關sql以最佳化或者處理。可以透過events來診斷

SESSION SET EVENTS
2 '1652 trace name errorstack level 1';

Session altered.

count(*) from (select * from alibaba.member order by member_level);

select count(*) from (select * from alibaba.member order by member_level)
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace FCPTEST

>

於是轉到 udump 目錄下找到剛才產生的trace，我們發現

[oracle@oradev udump]$ more ocndev_ora_15452.trc
/opt/oracle/admin/ocn/udump/ocndev_ora_15452.trc
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /opt/oracle/products/9.2.0
System name: Linux
Node name: oradev
Release: 2.4.9-e.3
Version: #1 Fri May 3 17:02:43 EDT 2002
Machine: i686
Instance name: ocndev
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 15452, image: oracle@oradev (TNS V1-V3)

*** SESSION ID:(85.639) 2004-08-24 17:50:19.030
*** 2004-08-24 17:50:19.030
ksedmp: internal or fatal error
ORA-01652: unable to extend temp segment by 128 in tablespace FCPTEST
Current SQL statement for this session:
select count(*) from (select * from alibaba.member order by member_level)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+269 call ksedst()+0 0 ? 0 ? 0 ? 0 ? 71417550 ?
70ECF9B8 ?
ksddoa()+446 call ksedmp()+0 1 ? AA703A8 ? 40622470 ?
674 ? 1 ? 406224D4 ?
ksdpcg()+521 call ksddoa()+0 40622470 ? AA703A8 ?
ksdpec()+220 call ksdpcg()+0 674 ? BFFE8D88 ? 1 ?
ksfpec()+133 call ksdpec()+0 674 ? 674 ? AA6D304 ?
BFFE8DBC ? 9812A41 ?

這裡面記錄了產生temp不足錯誤的sql。

開啟跟蹤
ALTER system SET EVENTS '1652 trace name context forever, level 1';
關閉跟蹤
ALTER system SET EVENTS '1652 trace name context off';

或者

開啟跟蹤
alter system set events '1652 trace name errorstack level 1';
關閉跟蹤
alter system set events '1652 trace name errorstack off';

也可在資料庫啟動前在初始化引數中設定

event = '1652 trace name errorstack level 1'

這樣對整個資料庫session都有效了。

實際上我們可以看出，event裡面設定了 1652 正好是sql導致的錯誤號，也就是說實際上錯誤號是和某個event相關聯的。對某個特定錯誤進行捕獲就可以透過類似這樣的方式進行。