Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: exp, dbms_stats, RMAN and rollback segments

RE: exp, dbms_stats, RMAN and rollback segments

From: Orr, Steve <>
Date: Thu, 29 May 2003 12:42:14 -0800
Message-ID: <>

Hi Kirti,

Sounds like you have the same suspicions as do we. So far as we know there isn't any heavy duty DML before the export and there isn't any other activity on the schema other than RMAN and DBMS_STATS. We don't understand how RMAN or DBMS_STATS ***could*** be the culprit but we've seen WEIRDNESS before.

We host our webapp and upgrades are customer driven. (Shivers and shudders!) Upgrades often entail database changes so the application shuts out end user access and kicks off an export before changing tables, munging data, updating the code, etc. This process has been fairly automagic and the export went along about 5 minutes before it crapped out. The only known difference between this upgrade run and other successful ones is that dbms_stats was running. So now I've copied the data into our test environment in an effort to duplicate the error and walk through our code. Luckily it's not written in Perl so it won't be too hard to read. :-)

Also, Walt found some delayed block cleanout issues on Metalink associated with analyzing indexes. The DBMS_STATS.GATHER_TABLE_STATS(...) routine in question is using the cascade option so I'm beginning to smell smoke. There are a few docs on Metalink about how ORA-01555's can occur even when NO updates are being performed but statistics are being gathered. WEIRDNESS!! See DocID's 367016.995; 17730.996; 45895.1; 89633.996; 61552.1. UNFORTUNATELY... the "solutions" proposed are not very appealing.

Meanwhile we're trying to convince others that Oracle is better than MySQL even though MySQL continuously updates its optimizer statistics and doesn't have problems like this. :-( And proposing a blockout period when customers are not allowed to upgrade while maintenance operations are going on would not be very well received. Especially since it's not an issue with MySQL and how we keep talking about how Oracle is a better 24X7 solution for our 24X7 webapp. Arghh!

Big sigh while contemplating a lot of tedious work and getting nowhere... And I had to get up at 2:30 A.M. this morning to help fix the outage.

Whine, whine, whine...

-----Original Message-----
Sent: Thursday, May 29, 2003 11:51 AM
Cc: Orr, Steve

 You may have to dig a little further...  What happened to those table(s) in that schema prior to starting the export? Heavy DML, may be?  

 This could be a case of 'delayed block cleanout'. Export triggered the cleanout and wanted to access the rollback segments.

 If no table data was modified after export started reading that table, then there is no need to read RBS info (except for the DBC case, IMO).

Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
Please see the official ORACLE-L FAQ:
Author: Orr, Steve

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Thu May 29 2003 - 15:42:14 CDT

Original text of this message