sqlplus stuck on delete query

Question

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;

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?

Answer 1

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

• 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:

SESSION 1

SQL> delete from emp where empno = 7369;

1 row deleted.

SESSION 2

SQL> delete from emp where empno = 7369;

Session 2 keeps waiting.

Let's check why:

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>

SESSION 1

SQL> rollback;

Rollback complete.

SQL>

SESSION 2

SQL> delete from emp where empno = 7369;

1 row deleted.

SQL>

Session 2 succeeded.

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!

Answer 2

I have found what the problem was.

I was executing the anonymous block form the cmd using sqlpus while my session with the sql-developer tool was still open. I closed the connection with the db and everything worked as expected.

I suppose there were some DML in that session and I didn't commit/rollback.

Thanks to @LalitKumarB and @AlexPoole for the insights.