ORA-01013: SQL Developer does not automatically rollback after query cancellation

Explanation

When you press the stop button in an Oracle SQL Developer worksheet, the client sends a cancel request to the server. Oracle Database responds with ORA-01013: user requested cancel of current operation. The cancellation only aborts the currently executing statement; it does not affect the transaction state. If autocommit is disabled, any INSERT/UPDATE/DELETE that was part of an open transaction remains active, holding locks until you issue an explicit COMMIT or ROLLBACK.

Confirmed Facts

• ORA‑01013 indicates statement cancellation, not transaction rollback.
• SQL Developer does not add its own rollback logic on cancellation; it relies on the session’s autocommit setting.
• If autocommit is ON, each DML statement is committed automatically, so no transaction remains open after cancellation.
• If autocommit is OFF, the transaction stays open and must be ended manually.

Steps to Resolve or Prevent the Issue

1. Check SQL Developer’s autocommit preference:
   • Navigate to Tools → Preferences → Database → Autocommit.
   • See whether Auto Commit is checked.
2. Enable autocommit for ad‑hoc worksheets (recommended if you only run isolated queries):
   • Check the Auto Commit box.
   • Click OK to save.
3. If you prefer to keep autocommit OFF, manually roll back after a cancellation:
   • In the worksheet, after seeing ORA‑01013, type:

   ROLLBACK;

   • Execute the statement (or press Ctrl+Enter).
4. Verify that the transaction is cleared:
   • Run:

   SELECT sid, serial#, status, taddr FROM v$session WHERE username = USER;

   • If TADDR is NULL, no transaction is active.
   • Alternatively, check locks:

   SELECT * FROM v$lock WHERE sid = (SELECT sid FROM v$session WHERE username = USER);

   • No rows mean locks are released.

When to Ask for More Detail

If you are using a custom connection plugin or a non‑standard JDBC driver that might override autocommit behavior, let me know the exact driver version and connection properties so I can confirm whether the above steps apply.