为什么我在从数据库视图中进行选择时会得到一个未完成的事务？
如果我在 pl/sql Developer 中对数据库表执行一个简单的 select 语句，我会得到一组标准的结果，正如我所期望的那样。
最近，我粘贴了一个来自存储过程的查询，该存储过程碰巧从视图中进行选择，并注意到事务似乎处于打开状态。 PL/SQL Developer 中提供的回滚和提交选项可以明显看出这一点。
对其他开发人员的一项民意调查显示，这似乎会影响某些开发人员，但不会影响其他开发人员，这使我怀疑 PL/SQL Developer 设置。
到底为什么会这样呢？视图 itelf 有一个到另一个数据库的 DBLink，但我不希望这有任何效果。
有什么想法吗？
If I execute a simple select statement in pl/sql developer against a database table, I get a standard set of results back as I would expect.
Recently, I pasted a query from a stored procedure that happened to select from a view, and noticed that a transaction was seemingly left open. This was appraent by the rollback and commit options were available in PL/SQL developer.
A poll of other developers revealed that this seems to affect some but not others, which lead me to suspect PL/SQL Developer settings.
Why on earth would this be the case? The view itelf has a DBLink to another database, but I wouldn't expect this to have any effect.
Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
与您的预期相反，数据库链接似乎是开放事务的来源。我以前在 PL/SQL Developer 中对远程表运行 SELECT 查询时注意到过类似的行为。
引用 Tom Kyte（来源）：
编辑：“任何 SQL 语句都会在 Oracle 中启动事务”？不，事实并非如此，这里有一个演示。此演示使用数据字典视图 V$TRANSACTION，其中列出了交易活跃。这一切都在我的本地 Oracle XE 数据库上运行，除了我之外没有其他用户连接到它。
我们将在本演示中使用下表。它仅包含一列：
SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(38) SQL> select count(*) from v$transaction; COUNT(1) ---------- 0目前没有活动交易。让我们针对该表运行 SQL 查询：
SQL> select * from test; A ---------- 2 SQL> select count(*) from v$transaction; COUNT(1) ---------- 0仍然没有活动事务。现在让我们做一些启动事务的事情：
SQL> insert into test values (1); 1 row created. SQL> select count(*) from v$transaction; COUNT(1) ---------- 1正如预期的那样，我们现在有一个活跃的事务。
SQL> commit; Commit complete. SQL> select count(*) from v$transaction; COUNT(1) ---------- 0提交事务后，它不再处于活动状态。
现在，让我们创建一个数据库链接。我正在使用 Oracle XE，下面创建了一个从 Oracle XE 实例返回自身的数据库链接：
现在让我们看看当我们通过数据库链接从表中进行选择时会发生什么：
SQL> select count(*) from v$transaction; COUNT(1) ---------- 0 SQL> select * from test@loopback_xe; A ---------- 2 1 SQL> select count(*) from v$transaction; COUNT(1) ---------- 1如您所见，只需从远程表中进行选择打开一个交易。
我不确定这里究竟要提交或回滚什么，但我必须承认我不知道分布式事务的来龙去脉，而答案可能就在其中。
Contrary to your expectation, it looks like the database link is the source of the open transaction. I've noticed behaviour like this before when running SELECT queries on remote tables in PL/SQL Developer.
To quote Tom Kyte (source):
EDIT: 'Any SQL statement starts a transaction in Oracle'? No, it does not, and here's a demonstration of it. This demonstration uses the data dictionary view V$TRANSACTION, which lists the active transactions. This is all running on my local Oracle XE database, which has no users other than me connected to it.
We'll use the following table during this demonstration. It contains only a single column:
SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(38) SQL> select count(*) from v$transaction; COUNT(1) ---------- 0No active transactions at the moment. Let's run a SQL query against this table:
SQL> select * from test; A ---------- 2 SQL> select count(*) from v$transaction; COUNT(1) ---------- 0Still no active transactions. Now let's do something that will start a transaction:
SQL> insert into test values (1); 1 row created. SQL> select count(*) from v$transaction; COUNT(1) ---------- 1As expected, we now have an active transaction.
SQL> commit; Commit complete. SQL> select count(*) from v$transaction; COUNT(1) ---------- 0After committing the transaction, it's no longer active.
Now, let's create a database link. I'm using Oracle XE, and the following creates a database link from my Oracle XE instance back to itself:
Now let's see what happens when we select from the table over the database link:
SQL> select count(*) from v$transaction; COUNT(1) ---------- 0 SQL> select * from test@loopback_xe; A ---------- 2 1 SQL> select count(*) from v$transaction; COUNT(1) ---------- 1As you can see, simply selecting from a remote table opens a transaction.
I'm not sure exactly what there is to commit or rollback here, but I have to admit to not knowing the ins and outs of distributed transactions, within which the answer probably lies.
任何 SQL 语句在 Oracle 中启动事务。
来自手册：
最有可能的是那些没有看到这一点的人正在自动提交中运行由语句启动的事务在该语句完成后立即提交的模式。
其他人声称
SELECT不是 DML，但同样手册明确指出：Any SQL Statement starts a transaction in Oracle.
From the manual:
Most probably those who are not seing this are running in auto-commit mode where the transaction started by a statement is immediately committed after the statement has finished.
Others have claimed that a
SELECTis not DML, but again the manual clearly states:您绝对不能严格使用普通查询来打开事务。您可以通过数据库链接打开一个。发布医生链接的人要么故意要么完全不小心遗漏了第二句话。
我不想在这里表现得像个巨魔，但是当人们只是在论坛上抛出答案来尝试这样做时，这真的很烦人。获得分数，答案完全是垃圾。
阅读文档的其余部分并首先测试它。
v$Session（针对您的会话）查看您是否有开放事务。 ) 到v$transaction如果有记录返回，则表示有交易，否则则没有交易。
You absolutely cannot open a transaction strictly with a normal query. You may open one across a database link. The guy who posted a link to the doctors either deliberately or utterly carelessly left out the 2nd sentence.
SELECT is neither a DML nor a DDL. It is also TRIVIAL to actually test this. I don't want to come off like a troll here, but its really annoying when people just throw out answers on a forum to try to get points and the answers are complete garbage.
Read the rest of the doc and TEST IT FIRST.
v$Session(for your session) tov$transaction.If a record comes back, you have a transaction. If not, you don't.
请注意，根据 Oracle 11g 管理指南 ，如果您跨数据库链接执行普通的旧 SELECT，您将启动一个需要提交（或回滚）的事务。
Note, according to the Oracle 11g Admin Guide, if you do a plain old SELECT across a database link you will start a transaction, which needs to be committed (or rolled back).
Select只是DML的一部分，但不获取锁，在插入/更新/删除/选择更新时获取行锁。 -罗斯是对的。
https://docs.oracle.com/cd/ E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
SELECT ... FROM table... ----- 无
INSERT INTO table ... 是 SX
与更新删除和选择更新相同。
Select is a part of DML only but lock is not acquired, A row lock is fetched upon insert/update/delete/ select for update. -Ross is right.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502
SELECT ... FROM table... ----- None
INSERT INTO table ... Yes SX
Same with update delete and select for update.
TLDR：从远程数据库中选择时，您还可以为远程数据库创建会话和连接。只要本地用户会话存在，该会话和连接就会持续存在。正如您可以猜测的那样，这可能会导致会话和连接的保持出现一些问题。
所以一定要提交：
从 emp@sales 中选择*；
犯罪;
我喜欢长读部分：
这也让我很烦恼，为什么从 db_links 中选择有一个事务
并决定最终结束这一切，所以从oracle文档：
Oracle®数据库管理员指南
11g 版本 2 (11.2)
https://docs.oracle.com/html/E25494_01/ds_appdev002.htm
控制数据库链接建立的连接
当在 SQL 语句或远程过程调用中引用全局对象名称时，数据库链接会代表本地用户建立与远程数据库中的会话的连接。仅当先前尚未为本地用户会话建立连接时，才会创建远程连接和会话。
与远程数据库建立的连接和会话在本地用户会话期间持续存在，除非应用程序或用户明确终止它们。请注意，当您跨数据库链接发出 SELECT 语句时，事务锁将被放置在撤消段上。要重新释放该段，必须发出 COMMIT 或 ROLLBACK 语句。
终止使用数据库链接建立的远程连接对于断开应用程序不再需要的高成本连接非常有用。您可以使用带有 CLOSE DATABASE LINK 子句的 ALTER SESSION 语句来终止远程连接和会话。例如，假设您发出以下事务：
SELECT * FROM emp@sales;
犯罪;
以下语句终止 sales 数据库链接指向的远程数据库中的会话：
ALTER SESSION CLOSE DATABASE LINK sales;
要关闭用户会话中的数据库链接连接，您必须具有 ALTER SESSION 系统权限。
笔记：
在关闭数据库链接之前，首先关闭使用该链接的所有游标，然后结束当前事务（如果它使用该链接）。
参见：
Oracle 数据库 SQL 语言参考 有关 ALTER SESSION 语句的更多信息
TLDR : On select from remote database you also create session and connection for remote DB. That session and connection persists as long as local user session. As you can guess this can lead to some problems with keeping up with session and connections.
SO ALWAYS DO A COMMIT :
SELECT * FROM emp@sales;
COMMIT;
I like a long read section :
This was bugging me also so much why there is an transaction on selects from db_links
and decided to finally end this so from oracle documentation :
Oracle® Database Administrator's Guide
11g Release 2 (11.2)
https://docs.oracle.com/html/E25494_01/ds_appdev002.htm
Controlling Connections Established by Database Links
When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.
The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them. Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the undo segments. To rerelease the segment, you must issue a COMMIT or ROLLBACK statement.
Terminating remote connections established using database links is useful for disconnecting high cost connections that are no longer required by the application. You can terminate a remote connection and session using the ALTER SESSION statement with the CLOSE DATABASE LINK clause. For example, assume you issue the following transactions:
SELECT * FROM emp@sales;
COMMIT;
The following statement terminates the session in the remote database pointed to by the sales database link:
ALTER SESSION CLOSE DATABASE LINK sales;
To close a database link connection in your user session, you must have the ALTER SESSION system privilege.
Note:
Before closing a database link, first close all cursors that use the link and then end your current transaction if it uses the link.
See Also:
Oracle Database SQL Language Reference for more information about the ALTER SESSION statement