Example of resumable option

An usually forgotten feature. Here there is a basic example:

SQL> CREATE TABLESPACE chinorri
2 LOGGING
3 DATAFILE 'K:\ORANT\DWH\chinorri.DBF' SIZE 128k
4 autoextend off EXTENT MANAGEMENT LOCAL SEGMENT
5 SPACE MANAGEMENT AUTO ;

Tablespace creado.

SQL> create table kk(kk varchar2(2000)) tablespace chinorri;
Tabla creada.

-- WITHOUT RESUMABLE, THE USUAL ERROR FOLLOWS
-- SIN RESUMABLE, LA PETADA DE SIEMPRE

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;


insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI

-- WITH RESUMABLE AND WAITING ONE MINUTE:
-- CON RESUMABLE DE UN MINUTILLO:
SQL> alter session enable resumable timeout 60 name 'resumable test';
Sesi¾n modificada.

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- OF COURSE, AT THE END OF THE PERIOD, THE TRANSACTIONS CRASHS
-- CLARO, AL CABO DEL MINUTILLO DA UN ERROR

insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-30032: la sentencia suspendida (de reanudaci¾n) ha sufrido un timeout
ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI


-- BUT IF WE CONFIGURE ENOUGH TIME AND SOLVE THE ISSUE IN THE MEANWHILE...
-- PERO SI LE DAMOS EL TIEMPO SUFICIENTE Y LO SOLUCIONAMOS ENTRE TANTO:


SQL> alter session enable resumable timeout 7200 name 'resumable test';
Sesi¾n modificada.


SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- HERE THE SESSION IS SUSPENDED.....
…….. Y AQUÍ SE QUEDA ESPERANDO DURANTE UNA HORA (7200 SEGUNDOS).

-- BUT WHEN WE RESIZE THE TABLESPACE:
-- Se amplia el TS y ……

42842 filas creadas.

SQL> alter session disable resumable;
Sesi¾n modificada.


Similarly you can use this clause in the import utility (resumable=Y and RESUMABLE_TIMEOUT=time).