Como reducir el UNDO Tablespace

1 – Verificar el UNDO Tablespace

SQL> show parameters undo_tablespace

 NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS1

2 – Crear un UNDO tablespace temporal

SQL> create undo tablespace UNDOTBS2  datafile '/u02/oradata/ORCL/undotbs2.dbf' size 100M;

Tablespace created.

3) Cambiar al nuevo Undo tablespace

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

System altered.

NOTA: Si se esta usando pfile en vez de spfile, se debe cambiar en el init.ora, luego bajar y subir la base.

4- Verificar

SQL> show parameters undo_tablespace

NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS2

5- Eliminar UNDO TABLESPACE original

SQL>drop  tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

6 – Crear el UNDO Tablespace

SQL>  create undo tablespace UNDOTBS1 datafile '/u02/oradata/ORCL/undotbs.dbf' size 100M autoextend on maxsize 4G;

Tablespace created.

7 – Cambiar por el definitivo

SQL> alter system set undo_tablespace='UNDOTBS1';

System altered.

8- Verificar

SQL> show parameters undo_tablespace

NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS1

9 - Eliminar Undo temporal

  SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

TROUBLESHOOTING

ERROR ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

Al ejecutar el drop puede dar este mensaje de error debido a que tiene transacciones activas.

  SQL> drop tablespace UNDOTBS2 including contents and datafiles

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

Tenemos varias opciones:

1 - Esperar a que finalicen y volver a ejecutar el DROP

2 - Eliminar las transacciones activas

3 - Bajar y subir la base de datos

OPCION 1 -Esperar a que finalicen y volver a ejecutar el DROP. 

Podemos ir controlando cuando lo libera en el alert.log.

En el alert.log cuando ejecutamos el DROP quedo el mensaje :

Wed Jan 29 12:24:34 ARST 2014

Successfully onlined Undo Tablespace 1.

Undo Tablespace 5 moved to Pending Switch-Out state.

*** active transactions found in undo tablespace 5 during switch-out.

Finalmente cuando no tiene mas transacciones activas aparece :

Wed Jan 29 12:29:32 ARST 2014

Undo Tablespace 5 successfully switched out.

 Volver a ejecutar: 

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

OPCION 2 - Consultar las transacciones activas

SQL> 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';


Se puede en este punto decidir matar las sesiones activas si no es posible avisar al usuario que cierre la sesion o esperar.

Luego volver a ejecutar: 

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

OPCION 3 - Bajar y subir la base. 
SQL>Shutdown immediate
SQL>startup

Luego volver a ejecutar: 

SQL> drop tablespace UNDOTBS2 including contents and datafiles;