The Oracle (tm) Users' Co-Operative FAQ

Why does it seem that a SELECT over a db_link requires a commit after execution ?

Back to index of questions

Because it does!  When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing.  This entry is held until the SQL statement is committed even if the SQL statement is a query.  A demonstration of this fact follows.  The REM’s were added to the output, which is otherwise a cut and paste of the screen.  The script db/obj/rbs_users is SQL to show user sessions to rollback segment assignments (transactions) and similar SQL can be found in the FAQ entry: Is there a way to detect processes that are rolling back, and can I figure out how long it will take?

REM  find the current session sid

 PFC> select * from v$mystat where rownum = 1;

        SID STATISTIC#      VALUE

 ---------- ---------- ----------

          7          0          1

REM  see who is using rollback, the current session should not be there

 PFC> @db/org/rbs_users

 no rows selected

REM  perform a remote query

 PFC> select count(*) from mpowel01.item_master@ut1.world;

   COUNT(*)

 ----------

       2603

REM  determine whether the current session is now a transaction or not

 PFC> @db/org/rbs_users

 NAME           USN    EXTENTS USERNAME            SID LOGON_TIM S STATUS

 ------------ ----- ---------- ------------ ---------- --------- --------

 START_TIME           T STATUS          USED_UBLK  USED_UREC

 -------------------- ---------------- ---------- ----------

 PROGRAM                                          TERMINAL

 ------------------------------------------------ ------------------------------

 ROLL01           2         20 MPOWEL01              7 05-SEP-01 ACTIVE

 09/05/01 12:34:36    ACTIVE                    1          1

 sqlplus@seqdev (TNS V1-V3)                       ttyiR/iARS

REM  end the transaction

 PFC> commit;

 Commit complete.

REM  verify the current session no longer shows as a transaction

 PFC> @db/org/rbs_users

 no rows selected

If the application code fails to issue a commit after the remote or distributed select statement then the rollback segment entry is not released.  If the program stays connected to Oracle but goes inactive for a significant period of time (such as a daemon, wait for alert, wait for mailbox entry, etc…) then when Oracle needs to wrap around and reuse the extent, Oracle has to extend the rollback segment because the remote transaction is still holding its extent.  This can result in the rollback segments extending to either their maximum extent limit or consuming all free space in the rbs tablespace even where there are no large transactions in the application.  When the rollback segment tablespace is created using extendable files then the files can end up growing well beyond any reasonable size necessary to support the transaction load of the database.  Developers are often unaware of the need to commit distributed queries and as a result often create distributed applications that cause, experience, or contribute to rollback segment related problems like ORA-01650 (unable to extend rollback).  The requirement to commit distributed SQL exists even with automated undo management available with version 9 and newer.   If the segment is busy with an uncommitted distributed transaction Oracle will either have to create a new undo segment to hold new transactions or extend an existing one.  Eventually undo space could be exhausted, but prior to this it is likely that data would have to be discarded before the undo_retention period has expired.

Note that per the Distributed manual that a remote SQL statement is one that references all its objects at a remote database so that the statement is sent to this site to be processed and only the result is returned to the submitting instance, while a distributed transaction is one that references objects at multiple databases.  For the purposes of this FAQ there is no difference, as both need to commit after issuing any form of distributed query.

See Oracle 8i Distributed Database Systems.

For Oracle 9.2 and 10g five chapters on Distributed Processing have been added to the DBA Administration manual.

Back to top

Back to index of questions