Problem solve Get help with specific problems with your technologies, process and projects.

RBS monitor to catch ORA-1555 errors

Why you get ORA-1555 errors and how to avoid them.

One of the most vexing errors in Oracle is this one:

ORA-1555: snapshot too old: rollback segment number 9 with name "R07" too small.

What causes this error? Oracle fails to return a consistent set of results (often called a snapshot) for a long-running query. This occurs because not enough information remains in the rollback segments to reconstruct the older data.

What are the reasons for the snapshot being too old?

  1. Too many transactions on the same rollback segment (after it reached its maximum size)
  2. Not enough space in the tablespace
  3. Tablespace reached its max extents number (max number of extents = TS extent size * TS max extents no / RBS extent size)
  4. Rollback segment reached its max extents number
  5. If the parameter MAXEXTENTS for the RBs is not unlimited (but has a value) then the RBs cannot grow to the maximum size of the TBS, rather to the multiple of extents_size by maxextents number.

You can avoid this error by creating more or larger rollback segments. Alternatively, long-running queries can be issued when there are few concurrent transactions, or you can obtain a shared lock on the table you are querying, thus prohibiting any other exclusive locks during the transaction.

Another reason is that you may be encountering the 'fetch across commits' problem. If you are committing within a cursor then you should close and re-open the cursor when you commit.

So what can you do to alert you to this situation? Here I introduce my RBS monitor. This monitor runs in the background, collecting information. If a 'snapshot too old' error occurs, you can return to the table's monitor, querying it to detect the exact problem and the time the error occurd.

Restrictions: The monitor is not magic. It can only detect errors occuring from using the RBS. It cannot detect problems occuring because of dblink to another database, or the sneaky 'fetch across commit' problem. If you don't find anything in the monitor, try checks in those directions.

The code consist of three parts: 1) A table in any schema in the database; 2) A shell script run on the Unix machine; and 3)Querying the table.

1. Create the table

create table CHECK_USED_OF_RBS
  my_date     DATE,
  NAME        VARCHAR2(30) not null,
  USERNAME    VARCHAR2(30) not null,
  SID         NUMBER,
  STATUS      VARCHAR2(8),
  SQL_TEXT    VARCHAR2(1000)
tablespace DATA_TBS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
    initial 256K
    next 256K
    minextents 1
    maxextents 505
    pctincrease 0
2. Shell & SQL scripts. You should create two files on your Unix machine: monitor_rbs.csh and monitor_rbs.sql. Here is the shell script monitor_rbs.csh:

setenv ORACLE_SID vanprod8
setenv ORACLE_HOME /export/home/oracle8
setenv PATH $ORACLE_HOME/bin:.:${PATH}
cd /export/home/oracle8/scripts/db_control

sqlplus db_control/db_control @monitor_rbs

Here is the SQL script run by the shell script: monitor_rbs.sql:

set echo on;
set timing on;
insert into check_used_of_rbs
select sysdate,, rs.extents, 
       ds.max_extents, rs.rssize,
       rs.optsize, du.username,
       se.sid, se.status,
       se.machine, sq.sql_text
  from v$transaction tr,
       v$rollstat rs,
       v$sql sq,
       v$session se,
       v$rollname rn,
       dba_users du,
       dba_segments ds
 where tr.xidusn = rn.usn
   and sq.PARSING_USER_ID = du.user_id
   and se.sql_address = sq.address
   and rs.usn = rn.usn
   and se.username = du.username
   and = ds.segment_name
   and tr.addr = se.taddr
   and sq.parse_calls = (select max(parse_calls) 
                           from v$sql sq2
                          where sq2.parsing_user_id = sq.parsing_user_id
                            and sq2.address = sq.address);
3. Querying the table. This query checks used of RBS in the last hour[s]. Change the time limit to suite your needs. Run it to check what hapened during the time of failure.

       to_char(my_date,'dd/mm/yy hh24mi') "date",
       name "rollback", extents, 
       max_extents "max_extents", ROUND(rssize/1024/1024,0) "size_MB",
       optsize/1024/1024 "optimal_MB", username "user_name",
       sid "session_id", status,
       machine "machine_name", sql_text
  from check_used_of_rbs
 where my_date > sysdate-1/24

For More Information

  • What do you think about this tip? E-mail the Editor at with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle error messages

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.