CREATE UNDO TABLESPACE

Colleghimoci ad un pluggable database come amministratore.

SQL> show con_name;

            CON_NAME
            --------------------------
            PDBUNDO

SQL> show user;

          USER è "PDBUNDO_ADMIN"

Query per estrarre la ddl del tablespace 

SQL> select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;

"  CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 

  '/u01/app/oracle/oradata/ORCL/pdbundo/undotbs01.dbf' SIZE 104857600

  AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M

  BLOCKSIZE 8192

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE"

SQL> show parameter undo_tablespace

NAME                    TYPE           VALUE    

--------------- ------ ---------------------------------

undo_tablespace     string         UNDOTBS1 

SQL>CREATE UNDO TABLESPACE 

    UNDOTBS_02 

    DATAFILE '/u01/app/oracle/oradata/ORCL/pdbundo/undotbs02.dbf'        SIZE 10M;

Impostarlo come tablespace di undo

SQL>  alter system set undo_tablespace='UNDOTBS_02' scope=both;

SQL> show parameter undo_tablespace

NAME                  TYPE     VALUE
--------------- ----------------- ----------
undo_tablespace    string         UNDOTBS_02

Creiamouna tabella ed effettuaiamo tante insert da saturare il tablespace undo.

create table test04 (x number, y varchar2(100)) ;

            begin

            for i in 1..400000

            loop

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

            end loop;

            end;

            /

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_02'

ORA-06512: at line 4

30036. 00000 -  "unable to extend segment by %s in undo tablespace '%s'"

*Cause:    the specified undo tablespace has no more space available.

*Action:   Add more space to the undo tablespace before retrying

           the operation. An alternative is to wait until active

           transactions to commit.

           

A questo punto o si modifica il tablepsace undo impostando autoextend on.

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/pdbundo/undotbs02.dbf' 

autoextend on

maxsize unlimited;

Rieseguendo lo script pl/sql vengono inserite nel tablespace undo

SQL> select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk

            from v$session a, v$transaction b 

            where a.saddr=b.ses_addr; 

SID    SERIAL#           USERNAME                   USED_UREC  USED_UBLK

---------- ---------- --------------------------------------------------------------------------------------

  99       3282            PDBUNDO_ADMIN             400000       3449

USED_UREC = numero di undo record occupati dalla transazione
USED_BLOCK = numero di undo block occupati dalla transazione 

La sessione 99 ha creato 400000  record all'interno dell'area UNDO contenuti in 3449 blocchi di undo.
Quindi nell'undo tablespace ho 3449 blocchi contenente 400000 righe modificate.

BEST PRACTICE

Oracle consiglia di non creare un secondo tablespace undo ma di lasciare quello definito in fase di installazione del db e agire su questo se occorre aumentare lo spazio.