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:

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

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:

Lets set resumable_timeout to a higher value:

Create the table again:

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

It clearly states that, the statement in suspended.

We can check dba_resumables also:

Now we will add some space to that tablespace,

Alert log now:

DBA_RESUMABLES:

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