Mir Sayeed Hassan – Oracle Blog

ORA-30032: The Suspended (Resumable) Statement has Timed Out in Oracle Database 11gR2(11.2.0.4)

Issue: This issue was occur while doing a transaction in real-time database & it’s was hung for few seconds & get the error

SQL> select instance_name, version, status, open_mode from V$database, v$instance;

INSTANCE_NAME VERSION STATUS OPEN_MODE
---------------- -------------------------------------------
mirtdb 11.2.0.4.0 OPEN READ WRITE

Connect to the specific user & create the table

SQL> create table testing1 as select * from dba_objects;
create table testing1 as select * from dba_objects
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out

Check the alert log of the database

[oracle@miroradb ~]$ tail -100 /u01/app/oracle/diag/rdbms/mirtdb/mirtdb/trace/alert_mirtdb.log

Mon May 02 09:41:26 2022
diag_adl:ORA-1652: unable to extend temp segment by 128 in tablespace TEST_2
diag_adl:statement in resumable session ‘User MIR_TBS(564), Session 312, Instance 1’ was suspended due to

Here its shows there is a space crunch on the tablespace MIR
It clearly shows, there was a space crunch on the tablespace MIR_TBS, therefore its suspended the sql statement for the sometime before throwing timeout error.

This can be resolve by increasing the time duration for the resumable_timeout

SQL> show parameter resumable_timeout

NAME                 TYPE       VALUE
--------------------------------------
resumable_timeout     integer    20

As you can see here the resumable_timeout is set in this database is 20 seconds., therefore any action like adding space to the tablespace need to be carried within that 20 seconds, to avoid timeout error,

To resolve this issue, we can increase the resumable_time to 900 seconds (15 minutes) therefore which will give us some more time, before it is being timed out.

SQL> alter system set resumable_timeout=900 scope=both;
System altered.

Verify

SQL> show parameter resumable_timeout

NAME                   TYPE         VALUE
------------- ----------------------------
resumable_timeout      integer       900

Now you can create a table successfully.

SQL> connect mir
Enter password:
Connected.

SQL> create TABLE TEST_3 as select * from dba_objects;
Table created.

=====Hence its tested & verified in our test env=====