ORA-30032: The Suspended (Resumable) Statement Has Timed Out

Let’s check the alert log:

Wed Jul 12 12:56:20 2023

diag_adl:ORA-1652: unable to extend temp segment by 128 in tablespace TEST_2

diag_adl:statement in resumable session 'User TESTUSER(202), Session 287, Instance 1' was suspended due to

diag_adl: ORA-01652: unable to extend temp segment by 128 in tablespace TEST_2

Wed Jul 12 12:56:32 2023

diag_adl:statement in resumable session 'User TESTUSER(202), Session 287, Instance 1' was timed out

It clearly shows, there was a space crunch on the tablespace TEST_2, which suspended the sql statement for the sometime before throwing timeout error.

That time duration is defined in resumable_timeout parameter

SQL> show parameter resumable_timeout

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

resumable_timeout integer 10

So the statement will be suspended for only 10 seconds. And any action like adding space to the tablespace need to be carried within that 10 seconds, to avoid timeout error.

Let’s set to a higher value i.e 3600 ( 1 hr), which will give us some more time, before it is being timed out.

SQL> alter system set resumable_timeout=900 scope=both;

System altered.

SQL> show parameter resumable_timeout

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

resumable_timeout integer 900