Re: A new form of ORA-1555

[Kirtikumar Deshpande]

A classic case when AUM does not help prevent 1555 errors !

 

Query duration is MAXQUERYLEN reported in v$undostat view. SCN could be the 'as of SCN' when the query started (not sure, as I could never get my small tests to fail with 1555 when using AUM).

 

What is also interesting is the SMON activity of offlining undo segments. That very well could be the reason for this error.

 

In this case adjusting UNDO_RETENTION > MAXQUERYLEN is one option. The other being, correcting this long running query.

 

BTW.. Raj & I exchanged e-mails earlier regarding this, as I just wanted to confirm that he was using Auto Undo Dmgt feature ;)

 

He informed me that v$undostat view did report a non-zero count in the SSOLDERRCNT column as expected... so that column works okay.

 

Cheers!

 

- Kirti

"Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:

A sighting in alert log ...

SMON offlining US=102
SMON offlining US=104
Fri Jun  6 08:42:06 2003
ORA-01555 caused by SQL statement below (Query Duration=41895 sec, SCN: 0x0010.c2bd0c24):
Fri Jun  6 08:42:06 2003
SELECT ROUND(G_1/:"SYS_B_00") G_1, ROUND(G_2/:"SYS_B_01") G_2, ROUND(G_3/:"SYS_B_02") G_3, ROUND(G_4/:"SYS_B_03") G_4, R

OUND(G_5/:"SYS_B_04") G_5, ROUND(A_1/:"SYS_B_05") A_1, ROUND(A_2/:"SYS_B_06") A_2, ROUND(A_3/:"SYS_B_07") A_3, ROUND(A_4

/:"SYS_B_08") A_4, ROUND(A_5/:"SYS_B_09") A_5, LOG_NO, AE, ADVR, AGNC, OFFICE, POB_ID, POB_CREATE_DT, POB_STAT  From (SE

LECT DECODE(YEAR,:"SYS_B_10",NVL((DECODE(QTR,:"SYS_B_11",NVL(CURR_GOAL,:"SYS_B_12"))),:"SYS_B_13"),:"SYS_B_14") G_1,DECO

DE(YEAR,:"SYS_B_15",NVL((DECODE(QTR,:"SYS_B_16",NVL(CURR_GOAL,:"SYS_B_17"))),:"SYS_B_18"),:"SYS_B_19") G_2,DECODE(YEAR,:

"SYS_B_20",NVL((DECODE(QTR,:"SYS_B_21",NVL(CURR_GOAL,:"SYS_B_22"))),:"SYS_B_23"),:"SYS_B_24") G_3,DECODE(YEAR,:"SYS_B_25

",NVL((DECODE(QTR,:"SYS_B_26",NVL(CURR_GOAL,:"SYS_B_27"))),:"SYS_B_28"),:"SYS_B_29") G_4,DECODE(YEAR,:"SYS_B_30",NVL((DE

CODE(QTR,:"SYS_B_31",NVL(CURR_GOAL,:"SYS_B_32"))),:"SYS_B_33"),:"SYS_B_34") G_5,DECODE(YEAR,:"SYS_B_35",NVL((DECODE(QTR,

:"SYS_B_36",NVL(CURR_ACCRUAL,:"SYS_B_37"))),:"SYS_B_38"),:"SYS_B
~

But I havn't figured out why query duration and SCN is shown any ideas? ... what is the significance? Oracle 9202, RAC

TIA
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !

*********************************************************************

---

Do you Yahoo!?
Free online calendar with sync to Outlook(TM).