resumable_timeout is an initialization parameter introduced in Oracle 9i.  This parameter defines the duration in seconds, for which the statements will be suspended if there is no space available in the tablespace for that operation. Once we add space to that tablespace, those transactions will resume their operation.

Let’s see the below example:

SQL> show parameter resumable_timeout                    

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout                    integer     0

The parameter is set to  ZERO. Let’s create a table.

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;
create table DBACLASS.TEST_TAB as select * from dba_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST2

As soon as the transaction couldn’t find any space, it terminated the process instantly( Because resumable_timeout is set to 0).

The alert log also:

Mon May 29 11:59:29 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEST2

Lets set resumable_timeout to a higher value:

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

System altered.

Create the table again:

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;
..
..

Now the query is stuck, let’s check the alert log:

Mon May 29 11:59:59 2017
ORA-1652: unable to extend temp segment by 128 in tablespace TEST2
statement in resumable session 'User SYS(0), Session 1103, Instance 1' was suspended due to
    ORA-01652: unable to extend temp segment by 128 in tablespace TEST2
Mon May 29 12:00:35 2017

It clearly states that, the statement in suspended.

We can check dba_resumables also:

SQL> select name, status, timeout, sql_text, error_msg from dba_resumable where status='SUSPENDED';

NAME                           STATUS       TIMEOUT SQL_TEXT
------------------------------ --------- ---------- ------------------------------
ERROR_MSG
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
User SYS(0), Session 1160, Ins SUSPENDED        900 create table DBACLASS.TEST_TAB
tance 1                                              as select * from dba_objects
ORA-01652: unable to extend temp segment by 128 in tablespace TEST2

Now we will add some space to that tablespace,

SQL> alter database datafile '/SIBIP16/SBIP18DB/SBIP18DB/test02.dbf' resize 1G;

Database altered.

Alert log now:

Completed: alter database datafile '/SIBIP16/SBIP18DB/SBIP18DB/test02.dbf' resize 1G
Mon May 29 12:00:40 2017
statement in resumable session 'User SYS(0), Session 1103, Instance 1' was resumed

DBA_RESUMABLES:

SQL> select name, status, timeout, sql_text, error_msg from dba_resumable where status='SUSPENDED';

no rows selected

We can see, as soon as space is available, the statement resumed its transaction

SQL> create table DBACLASS.TEST_TAB as select * from dba_objects;

Table created.