Subject:            ORA-1562 AND OPEN TRANSACTIONS IN ROLLBACK SEGMENTS
Author:             YLIEN
Last Revision Date: 30 July      1995



INTRODUCTION
============

A common error that you receive with rollback segments is ORA-1562. Most of the
time, that error is accompanied with another one, commonly ORA-1547. An
efficient way to solve this error is to find out who is issuing the query to
cause this error to occur. Unfortuneately, there is no direct way to find out
this information, nor a data dictionary view that will reveal this information.
This bulletin will discuss how to find out this information in both ORACLE6
and ORACLE7.

UNDERSTANDING
=============

When a user performs DML, the transaction is recorded in the rollback segment.
The transaction remains open in the rollback segment until the user commits the
changes.  If the user does not commit the changes, other users' transactions
will eventually wrap around the rollback segment and "bump into" the open
transaction.  At this point any new transactions will attempt to grab a new
extent for the rollback segment.  Eventually, if the initial transaction stays
open for long enough, the rollback segment will not be able to extend, and the
error ORA-1562 will occur for all users performing DML.  To solve the problem,
run the following query to determine who has open transactions, and have all
these users commit their changes.  This should solve the  ORA-1562.

ORACLE 6
========

This query will show all users that have active transactions, and the rollback
segment each user is writing to:

select  r.name,
        l.pid,
        p.spid,
        nvl(p.username,'no transaction') username,
        p.terminal
from    v$lock l, v$process p, v$rollname r
where   l.pid = p.pid(+)
and     trunc(l.id1(+)/65536) = r.usn
and     l.type(+) = 'TX'
and     l.lmode(+) = 6
order by r.name
/

Sample DEC VAX/VMS output:

NAME                                  PID SPID      USERNAME       TERMINAL
------------------------------ ---------- --------- -------------- --------
SYSTEM                                              no transaction
ROLLBACK1                               6 43E010FA  OPS$JSMITH     VTA413:
ROLLBACK2                               9 43E00C28  DEV16          VTA264:
ROLLBACK2                              10 43E00DE1  GUEST          VTA152:

The SYSTEM rollback segment does not have an active transaction, the ROLLBACK1
rollback segment has one active transaction, and the ROLLBACK2 rollback segment
has two active transactions.

ORACLE 7
========

In version 7, there is a slight change in the query as there are changes to the
data dictionary views.

  SELECT r.name "ROLLBACK SEGMENT NAME ",
                 p.pid "ORACLE PID",
                 p.spid "SYSTEM PID ",
                 NVL ( p.username , 'NO TRANSACTION'),
                 p.terminal
  FROM v$lock l, v$process p, v$rollname r
  WHERE  l.sid = p.pid(+)
  AND TRUNC (l.id1(+)/65536) = r.usn
  AND l.type(+) = 'TX'
  AND l.lmode(+) = 6
  ORDER BY r.name;

Here is a sample output:

ROLLBACK SEGMENT NAME          ORACLE PID SYSTEM PI NVL(P.USERNAME, TERMINAL
------------------------------ ---------- --------- --------------- ---------
R01                                                 NO TRANSACTION
R02                                     8 18477     pogupta         ttypc
R03                                                 NO TRANSACTION
R04                                     9 21057     ylien           ttyq1
SYSTEM                                              NO TRANSACTION

Here is another relevant query that is useful. It determines which user in the
database is using what rollback segment as opposed to the operating system
user as given in the previous query.

This query looks at v$session, as opposed to v$process.

select s.sid, s.username, r.name "ROLLBACK SEG"
  from v$session s, v$transaction t, v$rollname r
 where s.taddr=t.addr
  and  t.xidusn = r.usn;

Here is a sample output:

SID        USERNAME                       ROLLBACK SEG
---------- ------------------------------ ------------------------------
         8 AMY                            R02
         9 EMILY                          R04