[DB] Complete Guide to Resolving ORA-01013 Error: From Causes to Solutions

Complete Guide to Resolving ORA-01013 Error: From Causes to Solutions

Overview

Hello! Today, we'll take a detailed look at the ORA-01013 error, which is frequently encountered when using Oracle databases. This error occurs with the message "user requested cancel of current operation," but in reality, it's often not actually canceled by the user, which causes confusion.

In this article, I'll explain in detail the real causes of the ORA-01013 error and effective solutions. From timeout settings to query tuning, I'll cover everything you can apply directly in practice.

Table of Contents

1. What is ORA-01013 Error?

2. Actual Causes of ORA-01013 Error

3. Methods to Resolve ORA-01013 Error

4. Timeout Configuration by Environment

5. Frequently Asked Questions (FAQ)

---

#1. What is ORA-01013 Error?

The ORA-01013 error is an error that occurs in Oracle databases, and the exact error message is as follows:

ORA-01013: User requested cancel of current operation

This message means "user requested cancel of current operation." Looking at this message alone, it's easy to think that the user directly pressed the cancel button or stopped the operation. However, in most cases, the actual cause is completely different.

. . . . .

---

#2. Actual Causes of ORA-01013 Error

Unlike the message of the ORA-01013 error, in most cases, the user didn't actually cancel the operation. The real causes of this error are as follows:

1) Main Causes

① Query Execution Timeout: Occurs when the query execution time limit set in the application is exceeded.

② Connection Pool Shortage: Occurs when the database connection pool is fully used or insufficient and cannot handle new connections.

③ Automatic Cancellation by Application: If a query takes longer than expected, applications (web servers, WAS, etc.) may automatically cancel the operation.

. . . . .

2) Actual Scenarios

The actual scenario usually goes like this:

① Application requests a query to Oracle DB
② Query execution takes longer than the application's timeout setting
③ Application automatically cancels the operation when it doesn't receive a response
④ Oracle recognizes this as a "user cancel request" and returns the ORA-01013 error

Therefore, in most cases, it's not directly canceled by the user, but automatically canceled due to system or application settings.

---

#3. Methods to Resolve ORA-01013 Error

There are two main methods to resolve the ORA-01013 error:

1) Increasing Timeout Duration

If query execution takes a long time, you can increase the timeout setting to wait until the query completes.

(1) Timeout Configuration by Framework

① MyBatis per-query timeout configuration:

<!-- Set timeout to 120 seconds -->
<insert id="insertMemberData"
        parameterType="map"
        timeout="120">
    INSERT INTO members (...)
    VALUES (...)
</insert>

② MyBatis global timeout configuration:

<configuration>
    <settings>
        <!-- Set default timeout of 60 seconds for all queries -->
        <setting name="defaultStatementTimeout" value="60"/>
    </settings>
</configuration>

③ JDBC connection timeout configuration:

// Set query execution timeout to 60 seconds
Statement stmt = connection.createStatement();
stmt.setQueryTimeout(60);

④ Connection Pool configuration adjustment:

<!-- Tomcat DBCP configuration example -->
<Resource name="jdbc/OracleDB"
          auth="Container"
          type="javax.sql.DataSource"
          maxTotal="100" <!-- Increase max connections -->
          maxIdle="30"
          maxWaitMillis="10000" <!-- Increase connection wait time -->
          ...
/>

. . . . .

2) Reducing Execution Time Through Query Tuning

Increasing the timeout is only a temporary solution. The long-term solution is to optimize the query itself.

(1) Execution Plan Analysis

Use Oracle's EXPLAIN PLAN to analyze the query's execution plan and identify bottlenecks.

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE department_id = 100;

-- Check execution plan
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

(2) Adding Appropriate Indexes

Add indexes to frequently used WHERE clauses to improve query speed.

-- Create index on department_id column
CREATE INDEX idx_emp_dept
ON employees(department_id);

(3) Applying Query Optimization Techniques

① Remove unnecessary columns
② Use joins instead of subqueries
③ Split complex queries into multiple steps
④ Apply batch processing for large data processing

(4) Using Oracle Hints

Add hints to queries to instruct the optimizer to use a specific execution plan.

-- Hint example forcing index usage
SELECT /*+ INDEX(e idx_emp_dept) */
       *
FROM employees e
WHERE department_id = 100;

---

#4. Timeout Configuration by Environment

Let's look at how to configure timeouts in various environments.

1) Spring Framework

In application.properties or application.yml:

# HikariCP connection pool configuration
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=20

. . . . .

2) Hibernate/JPA

In persistence.xml:

<property name="hibernate.jdbc.batch_size"
          value="50"/>
<property name="hibernate.jdbc.fetch_size"
          value="50"/>
<property name="hibernate.jdbc.timeout"
          value="60"/>

. . . . .

3) Oracle JDBC Configuration

// Oracle JDBC driver configuration
Properties props = new Properties();
props.setProperty("oracle.jdbc.ReadTimeout",
                  "60000");
Connection conn = DriverManager.getConnection(url,
                                               props);

. . . . .

4) Oracle Server Configuration

-- Set session timeout (unit: seconds)
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 60;

-- Set resource limits
ALTER SYSTEM SET resource_limit=TRUE;

---

#5. Frequently Asked Questions (FAQ)

1) Q: The ORA-01013 error occurs intermittently. What should I do?

A: If it occurs intermittently, it's often because execution time gets longer when system load is high. First, analyze the execution plan and performance of that query, and if necessary, monitor server resources to identify bottlenecks.

. . . . .

2) Q: Is increasing the timeout always a good solution?

A: While it can solve the problem in the short term, it may not be advisable in the long term. The reason timeouts occur may be due to inefficient queries or system design problems. If possible, it's better to solve the fundamental problem through query tuning or system optimization.

. . . . .

3) Q: Are there similar timeout-related errors besides ORA-01013?

A: Yes, related errors include the following:

① ORA-03113: end-of-file on communication channel
② ORA-12170: TNS:connect timeout occurred
③ ORA-04021: timeout occurred while waiting to lock object

. . . . .

4) Q: How should I adjust timeout settings between application server and database server?

A: Generally, it's good to set the application server's timeout slightly longer than the database timeout. This way, the database times out first and returns an appropriate error response, which the application can then handle.

---

Conclusion

Unlike the message, the ORA-01013 error is not actually canceled by the user in most cases, but occurs due to timeout settings in the application or system. To effectively resolve this error:

① Short-term solution: Increase timeout settings or adjust Connection Pool configuration
② Long-term solution: Performance improvement through query tuning and index optimization

The important thing is not simply increasing timeout values, but identifying the root cause of why query execution takes long. By optimizing queries using performance analysis tools and execution plans, you can build a more efficient and stable system.

I hope you can effectively resolve the ORA-01013 error by referring to this guide and maintain stable database operations.

Thank you for reading this long article.

The end.