如何捕捉temp表空间出错的session信息和SQL

我们有时候会遇见这样的烦恼，在特定的时候总有某个大查询导致临时表空间出错。但我们总不能守侯着捕获相关sql以优化或者处理。可以通过events来诊断

 

sys@OCN>ALTER SESSION SET EVENTS
  2      '1652 trace name errorstack level 1';

Session altered.

sys@OCN>select 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

sys@OCN>sys@OCN>

 

于是转到 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相关联的。对某个特定错误进行捕获就可以通过类似这样的方式进行。