Managing Resumable Space Allocation

Resumable statement è un istruzione che dice al server Oracle, in caso di errori legati al superamento della quota associata ad un utente oppure al superamento dello di spazio del tablespace associato all'user, di sospendere lo script finchè non viene risolto il problema. 

Normalmente Oracle risponde con un messaggio di errore e blocca lo statement.

 

La sospenzione di uno script è dovuta alle seguenti condizioni:
- Out of space
- Maximum extents reached
- Space quota exceeded

Colleghiamaoci ad un pluggable database.

SQL> show con_name;
            CON_NAME
            ------------------------------
            PDBTS

SQL> show user;
            USER è "PDBTSADM"

SQL>  create tablespace tbsalert datafile '/u01/app/oracle/oradata/ORCL/pdbts/tbsalert01.dbf'

            size 10M 

            logging

            extent  management local

            segment space management auto;

SQL> create table test06 (x number, y varchar2(100)) tablespace tbsalert;

SQL> begin

            for i in 1..400000

            loop

            insert into test06 values (i,'resumable statement');

            commit;

            end loop;

            end;

            /

ORA-01653: unable to extend table PDBTSADM.TEST06 by 128 in tablespace TBSALERT

ORA-06512: at line 4

01653. 00000 -  "unable to extend table %s.%s by %s in tablespace %s"

*Cause:    Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.

*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Vengono inserite solo 272.815 su 400.000.

select count(*) from test06;

L'opzione Resumable space allocation può essere abilitata in due modi:

1) Impostare il parametro di inzializzazione RESUMABLE_TIMEOUT ad un valore diverso da zero.

   SQL>  show parameter resumable_timeout

2) SQL> ALTER SESSION ENABLE RESUMABLE

Il valore di attesa è impostato a 7200 sec = 2 ore.

Se volgiamo ridurre il tempo di sospensione:

SQL > ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600

Quindi rifacendo il giro di insert cosa otteniamo?

SQL>  truncate table test06;

SQL> begin

            for i in 1..400000

            loop

            insert into test06 values (i,'resumable statement');

            commit;

            end loop;

            end;

            /

Lo script non si ferma ed occorre aprire una nuova sessione e analizzare il problema.

Attulamente sono state inserit 272815 righe.

SQL> select count(*) from test06;

SQL> select status,  error_msg 

     from DBA_RESUMABLE;

STATUS                                                 ERROR_MSG
--------------------------------------------------------------------
SUSPENDED             ORA-01653: unable to extend table PDBTSADM.TEST06 by 128 in tablespace TBSALERT

Verifichiamo se il tablespace è auto estendibile.

SQL> SELECT file_name, autoextensible 

           FROM dba_data_files 

           WHERE  tablespace_name='TBSALERT';

FILE_NAME                                                                                      AUT

----------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbts/tbsalert01.dbf         NO

Eseguiamo la modifica del datafile

SQL>  ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/pdbts/tbsalert01.dbf'

            AUTOEXTEND ON 

            MAXSIZE UNLIMITED;

Dopo aver  eseguito questo comando la prima sessione termina con il comando 

"Procedura PL/SQL completata correttamente." e il numero di righe inserite è 400000.

Se interroghiamo nuovamente la query troviamo lo stato Normal e risegeundo la query dopo un pò di tempo non restituisce più record.

SQL> select status, error_msg
     from DBA_RESUMABLE;

STATUS                     ERROR_MSG 
-------------------------------------------
NORMAL