PROBLEM:
While doing a transaction, it was hung for few seconds and got the below error.
SQL> create TABLE TEST_3 as select * from dba_objects;
create TABLE TEST_3 as select * from dba_objects
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
CAUSE & SOLUTION:
Let’s check the alert log:
Wed Jul 12 12:56:20 2017
diag_adl:ORA-1652: unable to extend temp segment by 128 in tablespace TEST_2
diag_adl:statement in resumable session ‘User DBA_RAJ(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 2017
diag_adl:statement in resumable session ‘User DBA_RAJ(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