How to capture session information and SQL for errors in temp table space

Session

We sometimes encounter such a problem, at a certain time there is always a large query caused the temporary table space error. But we can't wait to catch the relevant SQL to optimize or process. Can be diagnosed through events.

Sys@ocn>alter Session SET EVENTS
2 ' 1652 trace name errorstack level 1 ';

Session altered.

Sys@ocn>select Count (*) from (SELECT * to Alibaba.member order by Member_level);

Select COUNT (*) from (SELECT * to 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>

So go to the Udump directory to find the trace we just produced, we found

[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 N Umber: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 * to 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?

This records the SQL that generated the temp-less error.

Turn on tracing
ALTER system SET EVENTS ' 1652 Trace name Context forever, Level 1 ';
Turn off tracing
ALTER system SET EVENTS ' 1652 trace name context off ';

Or

Turn on tracing
Alter system set events ' 1652 trace name errorstack level 1 ';
Turn off tracing
Alter system set events ' 1652 trace name errorstack off ';

You can also set the in initialization parameters before the database is started

event = ' 1652 trace name Errorstack Level 1 '

This will work for the entire database session.

In fact, we can see that the event set 1652 exactly the error number caused by SQL, which means that the error number is actually associated with an event. Capturing a particular error can be done in a similar way.