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

[email protected]>alter session set events
2 '1652 trace name errorstack level 1';

session altered.

[email protected]>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

[email protected]>[email protected]>

于是转到 udump 目录下找到刚才产生的trace，我们发现

[[email protected] 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: [email protected] (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相关联的。对某个特定错误进行捕获就可以通过类似这样的方式进行。