Re: How to identify whether or not client session get ORA-1652 of TEMP in RAC.

Have a look at resumable sessions and the 'after suspend' trigger. A 'real' 1652 will then put the session that has a problem into suspension and fire the after suspend trigger. Give it a timeout of (say) 10 seconds so that it really crashes the end-user afterwards, but make sure it logs the problem. It is possible that a 'spurious' 1652 that can be resolved by extent reallocation will not cause a suspension.

Test carefully. I haven't tried setting sessions resumable en masse, so there may be odd side-effects - especially on a RAC system.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"wangbin" <wangbin_at_start.com.au> wrote in message
news:2d15bd69.0311051955.736efc8f_at_posting.google.com...


> We use RAC 9.2.0.3 on redhat AS 2.1 . We often receive the following

> error in alert.log.

> "ORA-1652: unable to extend temp segment by 128 in tablespace

> TEMP"

>

> 65973.1 shows that it is expected behavior.

> "It has also been observed that in an OPS environmemt ORA-1652

> messages occur in the alert.log while the re-assignment from one

> instance's SEP free extents to the requesting one seems to be

working


> fine because client sessions do not get the ORA-1652. This suggests

> that the re-allocation is internally triggered by event 1652, and

the


> ORA-1652 is not surpressed from being written to the alert.log."

> On the other hand, the client sessions may get the ORA-1652 if large

> sorts are occurring.

>

> Is it possible to monitor how often the client sessions get the

error?


> The system is on Internet. I have received complain from our

customer


> once. I don't want to receive too much complain from our customers

> before I take an action.

>

> Thanks,

> Bin