Cómo recrear un UNDOTBS si el actual está lleno o inválido

Hola Amig@s,

Si por un casual, nuestro tablespace UNDO en Oracle 11g o 12c se ha llenado por alguna operación de rollback muy grande y los clientes nos bloquean la conexión o devuelven el error ORA-30036, tendremos que recrear uno nuevo e indicarle a Oracle que ese será su nuevo UNDOTBS,

1-Creamos un nuevo tbs undo.

CREATE UNDO TABLESPACE undotbs2
DATAFILE 'C:\ORACLE\ORADATA\TEST\undotbs201.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 5M;

2-Le indicamos a Oracle que utilice el nuevo TBS por defecto.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

3-Comprobamos que no haya ningún segmento de rollback pendiente en el UNDOTBS original.

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN ( SELECT segment_name  FROM dba_segments  WHERE tablespace_name = 'UNDOTBS1' );

4-Si la query no devuelve registros, entonces podremos eliminar el TBS original.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

5-En caso de que diga oracle que aún tiene operaciones pendientes, deberemos buscar la sesión que está con la operación pendiente y cerrarla. Se puede buscar la sesión con la siguiente query:

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
 SELECT segment_name
 FROM dba_segments
 WHERE tablespace_name = 'UNDOTBS1'
);

NAME       STATUS          USERNA        SID    SERIAL#
---------- --------------- ------ ---------- ----------
_SYSSMU8$  PENDING OFFLINE SCOTT         362          32754

6-Con el SID y SERIAL, matamos la sesión.

alter system kill session '362,32754';

7-Por último, volvemos a intentar eliminar el undotbs.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Espero que les haya resultado útil.
Salu2.

Share on Google Plus