Diminuir tablespace de UNDO

Os seguintes passos serão abordados para redimensionar (diminuir) a tablespace de UNDO:

- Criar uma tablespace de undo temporaria
- Fazer a nova tablespace de undo como padrão
- Verificar quando a tablespace antiga para de ser usada
- Dropar a tablespace antiga
- Recriar a tablespace antiga, mantendo seu nome
- Voltar a tablespace antiga como padrão
- Dropar a tablespace temporaria de undo
 
Parâmetros de UNDO:
   
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

Informações sobre os datafiles da tablespce de UNDO:

SQL> SELECT   file_name, bytes / 1024 / 1024 MB FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1';

FILE_NAME                                                  MB
-------------------------------------------------- ----------
/u01/oradata/TEST/undotbs01.dbf                         1765

Criar uma segunda tablespace de UNDO e setá-la como padrão:

CREATE UNDO TABLESPACE UNDOTBS_tmp DATAFILE '/u01/oradata/TEST/undotbs_tmp.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 5120M ONLINE BLOCKSIZE 8K;
ALTER SYSTEM SET undo_tablespace = UNDOTBS_tmp;

Acompanhar o uso da tablespace antiga (quando não retornar linhas, podemos dropá-la)

SELECT segment_name, xacts, v.status FROM v$rollstat v, dba_rollback_segs WHERE tablespace_name = 'UNDOTBS1' AND segment_id = usn;

As seguintes queries também oferecem outros tipos de monitoração dos segmentos da UNDO:

Quem está usando um particular segmento de UNDO:

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size';

Informações sobre a sessão que está usando UNDO:

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name in (select tablespace_name from dba_tablespaces where contents='UNDO');

Quantidade de extents das tablespaces de UNDO:
   
SELECT tablespace_name, segment_name, SUM (blocks), SUM (BYTES) / 1024
FROM dba_undo_extents
GROUP BY tablespace_name, segment_name;
   
Verificar se existe algum extent de undo pendente para dropar a tablespace:
   
SELECT segment_name, xacts, v.status
FROM v$rollstat v, dba_rollback_segs
WHERE segment_id = usn and v.status <>'ONLINE' and tablespace_name in (select tablespace_name from dba_tablespaces where contents='UNDO');

* OBS.: Enquanto existirem segmentos com status PENDING OFFLINE não será possível dropar a tablespace antiga. Quando essa query não retornar nenhuma linha,
podemos dropar a tablespace de UNDO antiga.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

Recriar a tablespace original com o tamanho adequado:

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/oradata/TEST/undotbs01.dbf' SIZE 128M AUTOEXTEND ON NEXT 128M MAXSIZE 1024M ONLINE BLOCKSIZE 8K;
ALTER SYSTEM SET undo_tablespace = UNDOTBS1;

Dropar a tablespace de undo temporária (quando esta terminar de ser usada por alguma sessão):

DROP TABLESPACE undotbs_tmp INCLUDING CONTENTS AND DATAFILES ;