Why does it seem that a SELECT over a db_link requires a commit after execution ?
http://www.jlcomp.demon.co.uk/faq/dblink_commit.htmlWhy does it seem that a SELECT over a db_link requires a commit after execution ? ------------------------------- 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
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.
很可能是你以前所做的DML未commit或rollback
也搞不清系统的处理，可能是提交给远方的数据库，然后进行select之类的吧
-------------------------------
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 [email protected];
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.