DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01013 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01013: User Requested Cancel of Current Operation

ORA-01013 is raised when Oracle receives a cancel signal for the currently executing operation, either from the user, the application, or an external process. This error is not a database defect — it simply means that someone or something interrupted the running SQL before it could complete. Understanding why the cancel was triggered is key to resolving and preventing this error.

Top 3 Causes

1. Application-Level Query Timeout

The most common cause is a query timeout set in the application layer (JDBC, ODBC, OCI). When a query exceeds the configured timeout, the driver sends a cancel request to Oracle, resulting in ORA-01013.

-- Identify long-running active sessions
SELECT s.sid,
       s.serial#,
       s.username,
       s.last_call_et AS elapsed_seconds,
       q.sql_text
FROM   v$session s
JOIN   v$sql q ON s.sql_id = q.sql_id
WHERE  s.status = 'ACTIVE'
  AND  s.last_call_et > 30
ORDER BY s.last_call_et DESC;
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Increase the query timeout in your application settings, or optimize the SQL so it completes within the allowed window.

2. Inefficient SQL Causing Full Table Scans

Slow queries caused by missing indexes, bad join conditions, or stale optimizer statistics often run long enough to trigger a user or automated cancel — producing ORA-01013.

-- Check execution plan for a problematic query
EXPLAIN PLAN FOR
SELECT order_id, customer_id, order_date
FROM   orders
WHERE  status = 'PENDING'
  AND  order_date >= TRUNC(SYSDATE) - 90;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

-- Add an index to eliminate Full Table Scan
CREATE INDEX idx_orders_status_date
    ON orders (status, order_date);
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Analyze the execution plan, create appropriate indexes, and gather fresh optimizer statistics.

-- Gather table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'ORDERS', CASCADE => TRUE);
Enter fullscreen mode Exit fullscreen mode

3. User Interrupt or OS-Level Process Kill

Users pressing Ctrl+C in SQL*Plus, or OS-level process termination (e.g., kill -9) during a running query, also generate ORA-01013 on the server side. This is especially common in batch or ETL jobs managed by shell scripts.

-- Check for killed or sniped sessions
SELECT sid,
       serial#,
       username,
       status,
       osuser,
       machine,
       last_call_et
FROM   v$session
WHERE  status IN ('KILLED', 'SNIPED');

-- Force-kill a problem session (requires DBA privilege)
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

-- Find the OS process ID for manual cleanup
SELECT p.spid, s.sid, s.serial#, s.username
FROM   v$process p
JOIN   v$session s ON p.addr = s.paddr
WHERE  s.sid = 123;
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Verify that batch scripts are not forcibly killing Oracle client processes. If ghost sessions remain, kill them at the OS level using the spid returned above.

Quick Fix Summary

Cause Fix
App timeout too short Increase timeout or tune SQL
Missing index / bad plan Create index, gather stats, use hints
User/OS interrupt Review batch scripts, clean up sessions

Prevention Tips

1. Use Oracle Database Resource Manager
Set automatic execution time limits per user group to gracefully terminate runaway queries before they trigger application-level cancels.

-- Create a resource plan directive with execution time limit
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan             => 'MY_PLAN',
        group_or_subplan => 'REPORTING_GROUP',
        comment          => 'Limit long-running reports',
        mgmt_p1          => 20,
        switch_time      => 120,        -- switch after 120 seconds
        switch_group     => 'LOW_GROUP'
    );
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Monitor and Baseline SQL Performance Regularly
Use AWR/ASH reports to proactively detect slow SQL before users or timeouts cancel them. Lock in good execution plans using SQL Plan Baselines.

-- Find top time-consuming SQL from memory
SELECT sql_id,
       ROUND(elapsed_time / executions / 1e6, 2) AS avg_secs,
       executions,
       SUBSTR(sql_text, 1, 80) AS sql_preview
FROM   v$sql
WHERE  executions > 0
ORDER BY avg_secs DESC
FETCH FIRST 10 ROWS ONLY;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00028 – Session killed by a DBA
  • ORA-03113 – End of file on communication channel (network drop)
  • ORA-03114 – Not connected to Oracle (lost connection after cancel)

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)