How to capture the TEMP table space error SESSION information and SQL

xiaoxiao2021-03-06  89

We sometimes meet such troubles, there is always a big query in a certain time to make a mistake in the temporary table. But we can't keep caught relevant SQL to optimize or handle. Can be diagnosed by 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 symptest by 128 in TableSpace FCPTEST

Sys @ OCN> SYS @ OCN>

So I transferred to the UDUMP directory to find the TRACE you have now, we found

[Oracle @ oradev udump] $ more ocndev_ora_15452.trc / opt / oracle / admin / ocn / udump / ocndev_ora_15452.trcOracle9i Enterprise Edition Release 9.2.0.3.0 - ProductionJServer Release 9.2.0.3.0 - ProductionORACLE_HOME = / opt / oracle / products /9.2.0System name: LinuxNode name: oradevRelease: 2.4.9-e.3Version: # 1 Fri May 3 17:02:43 EDT 2002Machine: i686Instance name: ocndevRedo thread mounted by this instance: 1Oracle process number: 15Unix 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.030ksedmp: Internal OR Fatal Errorora-01652: Unable to extend symp segment by 128 in TableSpace FCPTESTCurrent 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 () 220 Call Ksdpcg () 0 674? Bffe8d88? 1? Ksfpec () 133 Call KSDPEC () 0 674? 674? AA6D304? BffE8DBC? 9812A41? This recorded SQL that generated TEMP is not enough.

Open Tracking ALTER SYSTEM SET Events '1652 Trace Name Context Forever, Level 1'; Close Tracking ALTER SYSTEM SET Events '1652 Trace Name Context Off';

or

Open Tracking ALTER SYSTEM SET Events '1652 Trace Name Errorstack Level 1'; close tracking ALTER SYSTEM SET Events '1652 Trace Name ErrorStack Off';

You can also set it in the initialization parameter before the database is started.

Event = '1652 Trace Name Errostack Level 1'

This is valid for the entire database session.

In fact, we can see that 1652 in Event is exactly the error number caused by SQL, that is, the actual error number is associated with an Event. Capture to a particular error can be carried out in a similar manner.

转载请注明原文地址:https://www.9cbs.com/read-123878.html

New Post(0)