Oracle：如何查明是否有待处理的事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1299694/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How to find out if there is a transaction pending?
提问by Erich Kitzmueller
I'm looking for a way to find out if there are uncommited INSERT, UPDATE or DELETE statements in the current session. One way would be to check v$lock with the current sid, but that requires read access to v$lock, which is be a problem if the DBA doesn't want to grant it. Any other ways (other than keeping track of all database commands issued by the application)?
我正在寻找一种方法来确定当前会话中是否存在未提交的 INSERT、UPDATE 或 DELETE 语句。一种方法是使用当前的 sid 检查 v$lock，但这需要对 v$lock 的读访问权限，如果 DBA 不想授予它，这是一个问题。任何其他方式（除了跟踪应用程序发出的所有数据库命令）？
回答by Vincent Malgrat
you can check if your session has a row in V$TRANSACTION(obviously that requires read privilege on this view):
您可以检查您的会话是否有一行V$TRANSACTION（显然需要对此视图的读取权限）：
SQL> SELECT COUNT(*)
FROM v$transaction t, v$session s, v$mystat m
WHERE t.ses_addr = s.saddr
AND s.sid = m.sid
AND ROWNUM = 1;
COUNT(*)
----------
0
SQL> insert into a values (1);
1 row inserted
SQL> SELECT COUNT(*)
FROM v$transaction t, v$session s, v$mystat m
WHERE t.ses_addr = s.saddr
AND s.sid = m.sid
AND ROWNUM = 1;
COUNT(*)
----------
1
SQL> commit;
Commit complete
SQL> SELECT COUNT(*)
FROM v$transaction t, v$session s, v$mystat m
WHERE t.ses_addr = s.saddr
AND s.sid = m.sid
AND ROWNUM = 1;
COUNT(*)
----------
0
回答by Matthew Watson
This is the query I normally use,
这是我通常使用的查询，
select s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;
回答by Robert Durgin
SELECT * FROM V$TRANSACTION
WHERE STATUS='ACTIVE';
See: http://forums.oracle.com/forums/thread.jspa?threadID=691061
参见：http: //forums.oracle.com/forums/thread.jspa?threadID=691061
回答by DBA
Use the query below to find out pending transaction.
使用下面的查询找出待处理的交易。
If it returns a value, it means there is a pending transaction.
如果它返回一个值，则表示有待处理的事务。
Here is the query:
这是查询：
select dbms_transaction.step_id from dual;
select dbms_transaction.step_id from dual;
References:
http://www.acehints.com/2011/07/how-to-check-pending-transaction-in.htmlhttp://www.acehints.com/p/site-map.html
参考资料：
http:
//www.acehints.com/2011/07/how-to-check-pending-transaction-in.html http://www.acehints.com/p/site-map.html
回答by cagcowboy
回答by Vu Trong Trang
Matthew Watson can be modified to be used in RAC
Matthew Watson 可以修改以在 RAC 中使用
select t.inst_id
,s.sid
,s.serial#
,s.username
,s.machine
,s.status
,s.lockwait
,t.used_ublk
,t.used_urec
,t.start_time
from gv$transaction t
inner join gv$session s on t.addr = s.taddr;
回答by Peter Nosko
The easiest and most reliable solution is to try and start a transaction and see it if succeeds. If some code already started a transaction but has not yet issued any DML, then the V$TRANSACTION view won't show anything.
最简单和最可靠的解决方案是尝试启动一个事务并查看它是否成功。如果某些代码已经启动了一个事务但还没有发出任何 DML，那么 V$TRANSACTION 视图将不会显示任何内容。
In this example below, I handle the exception to raise a user-defined application error. To defer to an existing exception handler, just do a SET TRANSACTION and then immediately COMMIT to undo it.
在下面的这个示例中，我处理异常以引发用户定义的应用程序错误。要遵循现有的异常处理程序，只需执行 SET TRANSACTION，然后立即 COMMIT 撤消它。
DECLARE
transaction_in_progress EXCEPTION;
PRAGMA EXCEPTION_INIT(transaction_in_progress, -1453);
BEGIN
SET TRANSACTION NAME 'CHECK_FOR_TRANSACTION_ALREADY_SET';
COMMIT; -- end transaction
EXCEPTION
WHEN transaction_in_progress THEN
RAISE_APPLICATION_ERROR(-20000,'Transaction is already in progress');
END;
/
