Acutally, there nothing at the table level to 'enable' it for the FBQ (in 9i R1 and R2). In 9i R2, they introduced a 'flashback' privilege (object and System level). In 9i R1 one needed exec privilege on a package (dbms_flashback) to use FBQ.
SMON offlined Undo Segs because those had been idled, had expired undo_retention period, and current number of concurrent transactions dropped. AUS still left online were due to either 1) they had active transactions or 2) unexpired extents or 3) SESSIONS parameter dectated... Dan, you are correct. After AUS gets created it gets dropped only when the Undo TS is dropped. (Also, switching to a new UNDO TS does not drop old AUS from old unto TS.) Whether it comes on-line or not is solely dependent on the transaction load. And that may create the possibility of getting 'Out of Space' error, in case, if I resized the UNDO TS datafiles to a smaller value ;) - Kirti --- "Daniel W. Fink" <[EMAIL PROTECTED]> wrote: > 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 <mailto:[EMAIL PROTECTED]> > > To: Multiple recipients of list ORACLE-L > > <mailto:[EMAIL PROTECTED]> > > 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 ! > > > > > __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
