Shrink the Undo Tablespace Size

Il modo migliore per ridurre il tablespace di Undo è quello di passare a un nuovo tablespace
di Undo e rilasciare il vecchio tablespace Undo.
I passaggi sono:

1) Creare un nuovo tablespace di appoggio

SQL> 
create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/S11TEST1/S11TEST1/datafile/undotbs02.dbf'
SIZE 4096M AUTOEXTEND ON NEXT 5M MAXSIZE 5120M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

2) Impostarlo come nuovo tablespace di Undo
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

Verifica
SQL> show parameter undo_tablespace
------------------------------------------------------------
undo_management       string     AUTO
undo_retention             integer  1440
undo_tablespace          string     UNDOTBS2


3) Controllare lo stato dei segmenti di undo e determinare se tutti i segmenti nel vecchio uno tablespace sono offline.

select tablespace_name , status , count(*) 
from dba_rollback_segs 
group by tablespace_name , status;

Se ci sono segmenti di undo con stato diverso da offline, dobbiamo attendere che diventano offline in base al tempo di retention (select tuned_undoretention from  v$undostat)

select status,segment_name 
from dba_rollback_segs 
where status not in ('OFFLINE') 
and tablespace_name='UNDOTBS1';


4) Quando sono offline è possibile procedere al drop del vecchio tablespace ma prima

salvare lo script di creazione del tbs 1 prima di cancellarlo

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE

 '/u01/app/oracle/oradata/TEST01/undotbs01.dbf' SIZE 13195M AUTOEXTEND ON NEXT 5M MAXSIZE 32767M

ONLINE

RETENTION NOGUARANTEE

BLOCKSIZE 8K

FLASHBACK ON;

SQL> Drop tablespace <tablespace_name> including contents and datafiles;


Se si droppa un tablespce Uno ancora in uso compare il messaggio di errore seguente
SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

La seguente query  restituisce il Rollback Segment Number, il nome del rollback segment, stato del the rollback segment, undo tablespace name, address of transaction state object, session identifier, session serial number che identifica univocamente una session's objects, username, program, machine id e operating system client user name.

  SELECT 
   a.usn,
   a.name,
   b.status,
   c.tablespace_name,
   d.addr,
   e.sid, 
   e.serial#,
   e.username,
   e.program,
   e.machine, 
   e.osuser
   FROM 
   v$rollname a, 
   v$rollstat b, 
   dba_rollback_segs c, 
   v$transaction d, 
   v$session e 
   WHERE 
   a.usn=b.usn AND 
   a.name=c.segment_name AND
   a.usn=d.xidusn AND
   d.addr=e.taddr AND
   b.status='PENDING OFFLINE';

Da queste info possiamo contattare gli utenti specifici che hanno bloccato i segmenti di rollback, per eseguire il commit o il rollback della transazione.

ALTER SYSTEM KILL SESSION '<sid#>, <serial#>';

5) Ricreo il tbs UNOTBS01 e lo imposto come tablespace di default.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SCOPE=BOTH;

6) Controllo che UNDOTBS02 non abbia rollback segments attivi  e poi lo cancello