ORA-01013 - User Requested Cancel of Current Operation

ORA-01013: User Requested Cancel of Current Operation

The ORA-01013 error occurs when a user explicitly cancels a running Oracle operation, typically by pressing Ctrl+C in SQL*Plus or clicking cancel in a database tool. This is usually an intentional action but can sometimes indicate underlying performance issues.

Use at Your Own Risk

This documentation is for educational purposes only. Always test solutions in development environments before production use. See our disclaimer for full terms.

Error Details

• Error Code: ORA-01013
• Error Message: “user requested cancel of current operation”
• Error Type: User-Initiated Cancellation
• Severity: Information/Warning

Common Causes

1. Manual Cancellation

• User presses Ctrl+C during query execution
• User clicks “Cancel” button in GUI tools
• Application timeout triggers cancellation
• Session termination requests

2. Long-Running Operations

• Queries taking longer than expected
• Large data processing operations
• Complex joins or aggregations
• Full table scans on large tables

3. Application Timeouts

• Connection timeout settings
• Query timeout configurations
• Application-level timeouts
• Web server timeout limits

4. Performance Issues

• Missing indexes causing slow queries
• Resource contention and locks
• I/O bottlenecks
• Memory constraints

5. Automated Cancellations

• Scheduled job timeouts
• Resource manager limits
• Profile-based query timeouts
• Database resource limits

Examples and Scenarios

Example 1: Manual Query Cancellation

Scenario:

SQL> SELECT COUNT(*) FROM very_large_table WHERE complex_condition = 'value';
-- User presses Ctrl+C after waiting too long
-- ORA-01013: user requested cancel of current operation

Analysis:

-- Check query execution plan
EXPLAIN PLAN FOR
SELECT COUNT(*) FROM very_large_table WHERE complex_condition = 'value';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Check for missing indexes
SELECT
    table_name,
    column_name,
    num_distinct,
    density
FROM user_tab_col_statistics
WHERE table_name = 'VERY_LARGE_TABLE'
AND column_name = 'COMPLEX_CONDITION';

Example 2: Application Timeout

Scenario:

// Java application with query timeout
Statement stmt = connection.createStatement();
stmt.setQueryTimeout(30); // 30 seconds
ResultSet rs = stmt.executeQuery("SELECT * FROM complex_view");
// ORA-01013: user requested cancel of current operation (after 30 seconds)

Solution:

// Increase timeout or optimize query
stmt.setQueryTimeout(300); // 5 minutes

// Or use async processing
CompletableFuture<ResultSet> future = CompletableFuture.supplyAsync(() -> {
    try {
        return stmt.executeQuery(optimizedQuery);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
});

Example 3: Resource Manager Timeout

Scenario:

-- Query hits resource manager limits
SQL> SELECT /*+ FULL(t1) FULL(t2) */ *
     FROM table1 t1, table2 t2
     WHERE t1.id = t2.id;
-- ORA-01013: user requested cancel of current operation
-- (Cancelled by resource manager after exceeding CPU limits)

Analysis:

-- Check resource manager settings
SELECT
    plan,
    cpu_method,
    active_sess_pool_p1,
    queueing_p1
FROM dba_rsrc_plans WHERE plan = 'DEFAULT_PLAN';

-- Check current resource usage
SELECT
    username,
    cpu_wait_time,
    cpu_time,
    physical_reads,
    consistent_gets
FROM v$sess_time_model stm
JOIN v$session s ON stm.sid = s.sid
WHERE s.username IS NOT NULL;

Example 4: Scheduled Job Cancellation

Scenario:

-- Scheduled job exceeds time limit
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'LONG_RUNNING_JOB',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN long_procedure; END;',
        enabled => TRUE
    );
END;
/
-- Job cancelled due to timeout
-- ORA-01013: user requested cancel of current operation

Solution:

-- Set appropriate job timeout
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'LONG_RUNNING_JOB',
        attribute => 'MAX_RUN_DURATION',
        value => INTERVAL '2' HOUR
    );
END;
/

-- Or break job into smaller chunks
CREATE OR REPLACE PROCEDURE process_in_batches IS
    v_batch_size NUMBER := 10000;
    v_processed NUMBER := 0;
BEGIN
    LOOP
        -- Process batch
        UPDATE large_table
        SET status = 'PROCESSED'
        WHERE status = 'PENDING'
        AND ROWNUM <= v_batch_size;

        v_processed := SQL%ROWCOUNT;
        COMMIT;

        EXIT WHEN v_processed < v_batch_size;
    END LOOP;
END;
/

Example 5: Web Application Timeout

Scenario:

// PHP web application
ini_set('max_execution_time', 60); // 60 seconds
$stmt = $pdo->prepare("SELECT * FROM complex_report_view");
$stmt->execute();
// Script timeout occurs, Oracle receives cancel signal
// ORA-01013: user requested cancel of current operation

Solution:

// Increase execution time for reports
ini_set('max_execution_time', 600); // 10 minutes

// Or use background processing
$stmt = $pdo->prepare("INSERT INTO report_queue (report_type, parameters) VALUES (?, ?)");
$stmt->execute(['complex_report', serialize($params)]);

// Process reports asynchronously

Diagnostic Queries

Check Active Long-Running Queries

-- Find currently running long operations
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    sl.elapsed_seconds,
    sl.time_remaining,
    sl.sql_text,
    sl.message
FROM v$session_longops sl
JOIN v$session s ON sl.sid = s.sid
WHERE sl.time_remaining > 0
ORDER BY sl.elapsed_seconds DESC;

Monitor Session Activity

-- Check what sessions are doing
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.program,
    s.machine,
    s.last_call_et,
    sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.username IS NOT NULL
AND s.status = 'ACTIVE'
ORDER BY s.last_call_et DESC;

Check Resource Usage

-- Monitor resource consumption
SELECT
    s.username,
    s.sid,
    s.serial#,
    t.name,
    t.value
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname t ON st.statistic# = t.statistic#
WHERE s.username IS NOT NULL
AND t.name IN ('CPU used by this session',
               'physical reads',
               'consistent gets',
               'redo size')
ORDER BY s.username, t.name;

Check Timeout Settings

-- Check profile timeout settings
SELECT
    profile,
    resource_name,
    resource_type,
    limit
FROM dba_profiles
WHERE resource_name IN ('CPU_PER_CALL', 'CPU_PER_SESSION', 'CONNECT_TIME', 'IDLE_TIME')
ORDER BY profile, resource_name;

-- Check current session limits
SELECT
    p.profile,
    p.resource_name,
    p.limit,
    s.username
FROM v$session s
JOIN dba_users u ON s.username = u.username
JOIN dba_profiles p ON u.profile = p.profile
WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1)
AND p.resource_name IN ('CPU_PER_CALL', 'CONNECT_TIME');

Handling and Prevention

1. Query Optimization

-- Analyze slow queries
SELECT
    sql_id,
    sql_text,
    executions,
    elapsed_time/1000000 as elapsed_seconds,
    cpu_time/1000000 as cpu_seconds,
    disk_reads,
    buffer_gets
FROM v$sql
WHERE elapsed_time > 30000000  -- More than 30 seconds
ORDER BY elapsed_time DESC;

-- Create appropriate indexes
CREATE INDEX idx_table_column ON table_name(frequently_queried_column);

-- Update table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME');

2. Application Design

// Implement proper timeout handling
public class DatabaseOperation {
    private static final int DEFAULT_TIMEOUT = 300; // 5 minutes

    public ResultSet executeQuery(String sql, int timeoutSeconds) {
        try {
            Statement stmt = connection.createStatement();
            stmt.setQueryTimeout(timeoutSeconds);
            return stmt.executeQuery(sql);
        } catch (SQLException e) {
            if (e.getErrorCode() == 1013) {
                logger.warn("Query cancelled by user or timeout: " + sql);
                // Handle cancellation gracefully
            }
            throw e;
        }
    }
}

3. Resource Management

-- Create resource plan for better control
BEGIN
    DBMS_RESOURCE_MANAGER.CREATE_PLAN(
        plan => 'MIXED_WORKLOAD_PLAN',
        comment => 'Plan for mixed OLTP and reporting workload'
    );

    DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
        consumer_group => 'REPORTING_GROUP',
        comment => 'Group for reporting queries'
    );

    DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
        plan => 'MIXED_WORKLOAD_PLAN',
        group_or_subplan => 'REPORTING_GROUP',
        cpu_p1 => 30,
        max_est_exec_time => 3600 -- 1 hour limit
    );
END;
/

4. Monitoring and Alerting

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_long_operations IS
    CURSOR c_long_ops IS
        SELECT sid, serial#, elapsed_seconds, sql_text
        FROM v$session_longops
        WHERE time_remaining > 0
        AND elapsed_seconds > 300; -- 5 minutes
BEGIN
    FOR rec IN c_long_ops LOOP
        -- Log or alert on long operations
        DBMS_OUTPUT.PUT_LINE('Long operation detected: SID=' || rec.sid ||
                           ', Elapsed=' || rec.elapsed_seconds || 's');
    END LOOP;
END;
/

Best Practices

1. Query Design

• Use proper WHERE clause conditions
• Implement pagination for large result sets
• Avoid unnecessary SELECT * operations
• Use EXISTS instead of IN for subqueries

2. Application Architecture

• Implement asynchronous processing for long operations
• Use connection pooling with appropriate timeouts
• Design for graceful cancellation handling
• Provide user feedback for long operations

3. Database Configuration

• Set reasonable timeout values
• Implement resource management
• Monitor query performance regularly
• Maintain proper indexing strategy

4. User Experience

• Provide progress indicators
• Allow users to cancel operations
• Implement background processing
• Set realistic user expectations

Recovery Actions

When ORA-01013 Occurs

1. Check if cancellation was intentional
2. Verify query performance and optimization
3. Review timeout settings
4. Check resource contention
5. Consider breaking large operations into smaller chunks

Preventive Measures

-- Implement query hints for better performance
SELECT /*+ INDEX(t, idx_table_column) */
       column1, column2
FROM table_name t
WHERE condition = 'value';

-- Use parallel processing for large operations
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(target_table, 4) */
INTO target_table
SELECT /*+ PARALLEL(source_table, 4) */ *
FROM source_table;

Related Topics

• Query Performance Tuning
• Resource Management
• Connection Timeout Configuration
• ORA-00054: Resource Busy

Summary

ORA-01013 is typically an expected result of user-initiated cancellations, but frequent occurrences may indicate performance issues requiring optimization. Proper handling involves implementing appropriate timeouts, optimizing queries, and designing applications to handle cancellations gracefully while providing good user experience.