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?

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.