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

wangbin_at_start.com.au (wangbin) 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

With appropiate exception handlers in the application it is possible to log the occurence of 1652 in a table. Other than that as this 1652 isn't surpressed from the alert, you could just grep the alert and pipe it to wc -l.
If you are running on NT, you could install Cygwin to obtain grep.

Sybrand Bakker
Senior Oracle DBA Received on Thu Nov 06 2003 - 02:38:52 CST