Resumable operations are operations that can be suspended instead of failing, allowing us to fix the problem and resume the operation. When importing data using impdp, if (for example) a tablespace gets full, instead of failing the entire import (and force us to restart it from the beginning), we can identify that and add some space to the tablespace while the import is waiting.
The resumable operations feature is managed by the RESUMABLE_TIMEOUT parameter. In an import I executed, I got the “ORA-39171: Job is experiencing a resumable wait” error, while my RESUMABLE_TIMEOUT parameter was 0 (that means that the feature is turned off).
In this case I preferred the import to fail and not wait (because it was running overnight, and it would fail anyway because I wasn’t connected to fix the problem on time). But this is where things started to get strange.
First, I couldn’t find anywhere a parameter to change the resumable option in impdp. I couldn’t find anything in the impdp documentation or in the output of “impdp help=y”. So I did what I had to do and turned on SQL tracing. Then I found this in the trace file:
ALTER SESSION ENABLE RESUMABLE NAME ‘SYS.SYS_IMPORT_FULL_01’
This means that the impdp itself turns the resumable feature on. OK, but what is the timeout? I checked the “alter session” documentation chapter where they show that you can configure the timeout or not, but wouldn’t say the default. So in the end I found it in the resumable operation chapter where they say that the default is 2 hours.
What’s left now is to understand how to control it, and according to my research, we can’t. There are “resumable” and “resumable_timeout” parameters, but they exist to support a legacy mode and are being ignored. So I tried to change my resumable_timeout parameter in the database to a value of 1. According to Oracle, impdp should take this value, but apparently it doesn’t and DBA_RESUMABLE showed me that the timeout for the import is still 7200 seconds.
The only thing I can think of is logon trigger, and I really don’t want to go there…
By the way, this is 18.104.22.168 database. If you have any idea about that, please add a comment to this post.