How to capture session information and SQL statements for temp tablespace errors

Sometimes we encounter such troubles. at a specific time, a large query may cause a temporary tablespace error. However, we cannot always capture related SQL statements for optimization or processing. You can use events for diagnosis.

 

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>

 

Go to the udump directory and find the generated 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) 17:50:19. 030
* ** 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?

 

The SQL statement that produces the temp deficiency error is recorded here.

 

Open a trail
Alter system set events '1652 trace name context forever, level 1 ';
Disable Tracing
Alter system set events '1652 trace name context off ';

Or

Open a trail
Alter system set events '1652 trace name errorstack level 1 ';
Disable Tracing
Alter system set events '1652 trace name errorstack off ';

 

You can also set the parameter in initialization before starting the database.

Event = '1970 trace name errorstack level 1'

This applies to the entire database session.

 

In fact, we can see that 1652 In the event is the error code caused by SQL, that is, the error code is actually associated with an event. You can capture a specific error in a similar way.