sqlplus卡在删除查询上 [英] sqlplus stuck on delete query

问题描述

在使用由SQL * Plus启动的匿名PL/SQL块时，我遇到一个奇怪的问题.该块包含一条delete语句，如果由oracle sql开发人员接口启动，它将按预期工作.如果我通过sqlplus从cmd启动它，它将卡住. 我发现了delete语句是问题所在……我遇到了类似的简单代码:

I'm experiencing a strange issue while using a an anonymous PL/SQL block launched by SQL*Plus. This block contains a delete statement and it works as expected if launched by the oracle sql developer interface. It gets stuck if I launch it from the cmd through sqlplus. I discovered the delete statement is the problem... I have the same situation with a simpler code like that:

set serveroutput on size 1000000
begin
  DELETE FROM USER_LEAD;
  dbms_output.put_line('test');
end;
/
exit;

如果删除delete语句，则一切正常，并且与我的真实匿名块相同.我想指出的是，我是唯一使用db的人，所以我认为该表没有被另一个用户/进程锁定.

If I remove the delete statement everything works and it's the same with my real anonymous block. I want to point out that I'm the only one using the db so I don't think the table is locked by another user/process.

有人遇到过这个问题吗?

Has anyone experienced that problem?

推荐答案

这可能是一件很愚蠢的事情，但是，这种情况在大多数情况下都会发生.

This might be a silly thing to find out, however, this happens most of the times.

• 您在一个会话中进行了 DML 交易.
• 您尚未提交/回滚该会话.
• 您打开另一个会话并执行另一个DML.
• 您发现查询保持等待中.

• You do a DML transaction in one session.
• You are yet to COMMIT/ROLLBACK that session.
• You open another session and do another DML.
• You find the query keeps waiting.

有关您的情况的简短演示:

A small demo of what happened in your case:

会议1

SQL> delete from emp where empno = 7369;

1 row deleted.

第二届会议

SQL> delete from emp where empno = 7369;

会议2一直在等待.

让我们检查一下原因:

SQL> SELECT
  2     s.blocking_session,
  3     s.sid,
  4     s.serial#,
  5     s.seconds_in_wait
  6  FROM
  7     v$session s
  8  WHERE
  9     blocking_session IS NOT NULL;

BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------
             373        130      11069              44

SQL>

会议1

SQL> rollback;

Rollback complete.

SQL>

第二届会议

SQL> delete from emp where empno = 7369;

1 row deleted.

SQL>

会话2成功.

SQL> SELECT
  2     s.blocking_session,
  3     s.sid,
  4     s.serial#,
  5     s.seconds_in_wait
  6  FROM
  7     v$session s
  8  WHERE
  9     blocking_session IS NOT NULL;

no rows selected

SQL>

因此，没有更多的会话在等待！

So, no more sessions waiting!

这篇关于sqlplus卡在删除查询上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！