Oracle-L: RE: Frequent ''ORA -1650: unable to extend rollback segment error

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Mon, 01 Jul 2002 12:03:24 -0800
Message-ID: <F001.0048D298.20020701120324@fatcity.com>

Have you checked if someone might be holding space in the rollback segment you're getting the error for? We had a similar problem two weeks ago which we resolved by killing the session that was holding the space (oddly, they weren't holding any locks so I don't know why they were holding RBS space). You can use this script (from the remarkably useful SQL & PL/SQL Annotated Archives) to find out:

column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
select R.name rr,
        NVL(S.Username, 'no transaction') us,
        S.Osuser os,
        S.Terminal te

from v$lock l, v$session s, v$rollname r where l.sid=s.sid(+)
and trunc(l.id1/65536)=r.usn
and l.type='TX'
and l.lmode=6
order by r.name;

-----Original Message-----
Sent: Monday, June 24, 2002 6:38 PM
To: Multiple recipients of list ORACLE-L errors'' in the

                                                                            
 Hi All,                                                                    
                                                                            
                                                                            
                                                                            
                                                                            
                                                                            
 For the last 10-12 days Database is giving frequent ORA 1650 errors. My    
 Database size is 74 GB and Total No. of Rollback segments are 19, with     
 Initial Extent 60MB, Next extent 60MB, MIN_EXTENTS 2, MAX_EXTENTS 505.     
 There are 2 Tablespaces holding these Rollback segments( 4GB and 1 GB)     
 There is no extra load on the database. I am really worried why this       
 problem has been started all of a sudden.

The rollback segment statistics is showing high number of Waits and wrap. For few rollback segments wait statistics it is exceeding even 100 which I

 have never seen. and now because of this few importants reports are        
 aborted.                                                                   
                                                                            
                                                                            
                                                                            
 Mon Jun 24 00:49:48 2002                                                   
 Thread 1 advanced to log sequence 5416                                     
 Current log# 2 seq# 5416 mem# 0: /oracle05/oradata/GASPROD/redo02.log      
 Mon Jun 24 00:49:55 2002                                                   
 ORA-1650: unable to extend rollback segment RBS12 by 7680 in tablespace    
 RBS                                                                        
                                                                            
 Failure to extend rollback segment 12 because of 1650 condition            
 Mon Jun 24 00:52:33 2002                                                   
 Thread 1 advanced to log sequence 5417                                     
 Current log# 1 seq# 5417 mem# 0: /oracle05/oradata/GASPROD/redo01.log      
 Mon Jun 24 00:56:24 2002                                                   
 Thread 1 advanced to log sequence 5418

Kindly suggest if anybody has some clue.

Regards
deepender

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Deepender.Gupta_at_newpower.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------