I believe that this is a new error message format for 9. The solution that is commonly quoted is to increase your undo_retention to cover the amount of time needed to complete the query. However, this is not a guarantee that the query will actually work. If space is needed, unexpired extents will be reused.
I think you may be hitting a situation similar to what Rachel encountered several weeks ago. It appears that a large number (at least 104) undo segments have been created. SMON is now offlining them in the hopes of reclaiming space. This brings up a situation posed recently and it was not a condition I had tested. If a large number of segments is created, are they ever dropped or do they continue to exist and consume space (at least 2 extents/128k) in the undo tablespace until it is dropped and a new one created? I THINK that the answer is that once created an undo segment will exist until the tablespace is dropped as there is no 'drop undo segment' command with aum.
-- Daniel W. Fink http://www.optimaldba.com
Ruth Gramolini wrote:
A new form of ORA-1555 It might have something to do with setting the table to do a Flashback query. Maybe Dan will know.Ruth----- Original Message -----From: Jamadagni, RajendraSent: Friday, June 06, 2003 10:00 AMSubject: A new form of ORA-1555
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, ROUND(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 !