RE: A new form of ORA-1555

[Gogala, Mladen]

Flashback query is enabled by setting the UNDO_RETENTION parameter to the

desired number of seconds. Table V$UNDOSTAT contains the column MAXQUERYLEN

which gives the max time that you can use to dynamically set UNDO_RETENTION.

You also have to enable flashback for session using DBMS_FLASHBACK.

 

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:[EMAIL PROTECTED]

-----Original Message-----
From: Daniel W. Fink [mailto:[EMAIL PROTECTED]
Sent: Friday, June 06, 2003 5:01 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: A new form of ORA-1555

You do not have to specifically enable a table for flashback query. In 9.2 (Kirti, please correct me if I am wrong), it is automatically enabled, even if you are not using AUM.

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:

It might have something to do with setting the table to do a Flashback query.  Maybe Dan will know.

 

Ruth

----- Original Message -----

From: Jamadagni, Rajendra

To: Multiple recipients of list ORACLE-L

Sent: Friday, June 06, 2003 10:00 AM

Subject: 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, 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 !