Our nightly batch job extracts customer data from a legacy Oracle database using a database connector. The job consistently fails after running for about 45 minutes with SQLTimeoutException. This is halting our automation process that depends on this data.
The query retrieves approximately 500K records with multiple joins across 5 tables. We’ve set the query timeout to 3600 seconds, but it still fails. The batch processes records in chunks of 10K.
java.sql.SQLTimeoutException: ORA-01013: user requested cancel of current operation
at DatabaseConnector.executeQuery(DatabaseConnector.java:245)
We’ve noticed that if the job fails, it leaves partial data in the staging tables, requiring manual cleanup before retry. How can we optimize the SQL query, configure proper timeouts, and handle partial data cleanup automatically?
Your query needs optimization. Post your SQL so we can review it. Common issues with multi-table joins include missing indexes on join columns, inefficient join order, and full table scans. Use EXPLAIN PLAN to identify bottlenecks. Also, consider adding a WHERE clause to filter by date ranges if you’re processing incremental data rather than full extracts. Processing 500K records nightly when maybe only 10K changed is wasteful.
For partial data cleanup, implement a two-phase approach. First, use a staging table with a batch run identifier. When the batch starts, generate a unique run ID and tag all extracted records with it. If the batch fails, your cleanup routine can delete all records with that run ID. Only mark the batch as successful and promote data to final tables after complete extraction. This prevents polluting your production data with incomplete loads. Wrap the promotion step in a transaction for atomicity.
The ORA-01013 error suggests the database is canceling the operation, possibly due to resource limits or locks. Check your Oracle database for long-running query limits and session timeout settings. Also, 500K records with 5-table joins in a single query is inefficient. Consider breaking it into smaller queries or using indexed views to pre-join the data.