UNDO Monitoreo, Mantenimiento y Tunning. ORACLE10gR2

 

http://www.ots.ac.cr/tech/content/undo-monitoreo-mantenimiento-y-tunning-oracle10gr2

 

El crecimiento indiscriminado del tablespace de UNDO puede volverse un gran problema, además de indicarnos que algo no anda bien, por otro lado un UNDO muy pequeño puede significar que ciertas instrucciones no terminen correctamente arrojando el siguiente error:

ORA-01555 snapshot too old: rollback segment number [string] with name "[string]" too small

A continuación algunas formas para monitorear el estado del UNDO, las transacciones que más UNDO requieren y técnicas para setear el tamaño más adecuado.

Primero revisaremos los parámetros referentes al UNDO:

SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————-
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

 

UNDO_MANAGEMENT: Parámetro dinámico (alter system set undo_management = <valor>) disponible desde 9i y que libera a los DBA de la administración y monitoreo del UNDO.

UNDO_RETENTION: Parámetro dinámico (alter system set undo_retention= <valor>) que indica en segundos  (por defecto 900) cuanto tiempo ha de permanecer, al menos, la información de UNDO disponible.

UNDO_TABLESPACE:Parámetro dinámico (alter system …) que indica el espacio de almacenamiento de “UNDO” a usar en el arranque. Si se indica en modo manual, provoca error y falla el arranque.

Para este caso setearemos la retención de UNDO a 600 segundos:

SQL> alter system set undo_retention=600;

Segmentos de UNDO

Con la siguiente query podemos ver el estado de los segmentos de UNDO:

SELECT SYSDATE AS fecha,
       unexpired.unexpired,
       expired.expired,
       active.active
FROM   (SELECT Sum(bytes / 1024 / 1024) AS unexpired
        FROM   dba_undo_extents
        WHERE  status = ‘UNEXPIRED’) unexpired,
       (SELECT Sum(bytes / 1024 / 104) AS expired
        FROM   dba_undo_extents tr
        WHERE  status = ‘EXPIRED’) expired,
       (SELECT CASE
                 WHEN Count(status) = 0
                 THEN 0
                 ELSE Sum(bytes / 1024 / 1024)
               END AS active
        FROM   dba_undo_extents
        WHERE  status = ‘ACTIVE’) active

Por ejemplo el resultado para esta prueba es:

Resultado:

Donde,

UNEXPIRED significa que estos segmentos de UNDO no contienen ninguna transacción activa, pero estos contienen transacciones que todavía son requeridos para FLASHBACK.
EXPIRED significa que estos segmentos no son requeridos después del periodo de retención definido en undo_retention.
ACTIVE significa que estos segmentos de UNDO contienen transacciones activas, o sea, no se ha realizado commit.

Los valores son en MB.  He creado una tabla llamada undo_utilizado donde se guarda el retorno de esta query cada 20 segundos para luego realizar un a análisis más fino.

Por ejemplo ejecutaremos un insert sin dar commit:

SQL> set time on
17:39:04 SQL>
17:39:06 SQL>
17:39:06 SQL> @insert.sql
547306 rows created.

17:39:15 SQL>

El comportamiento de los segmentos de UNDO es:

SELECT   * 
FROM     test.undo_utilizado 
ORDER BY fecha DESC;

Resultado:

Como se puede observar, actualmente existen 2,9375 MB de segmentos en estado ACTIVE y 0,0625 MB en estado UNEXPIRED. Si ejecutamos un commit para la sesión ocurre lo siguiente:

17:44:59 SQL> commit;

Commit complete.

17:45:01 SQL>

 

SELECT   * 
FROM     test.undo_utilizado 
ORDER BY fecha DESC;

Resultado:

Como se puede observar, luego de realizar commit, ya no existen segmentos en estado ACTIVE y estos pasaron a ser segmentos en estado UNEXPIRED.

En el tablespace de UNDO, los segmentos en estado UNEXPIRED significan espacio utilizado.

Un análisis de lo ocurrido con el siguiente grafico:

En el grafico podemos identificar los siguientes eventos:

• 17:39:06, aumento segmentos en estado ACTIVE de 0 MB a 2,9375 MB, esto debido a la instrucción de insert realizada. El insert tardó 11 segundos.

• 17:44:59, aumento de segmentos en estado UNEXPIRED de 0,0625 MB a 3 MB y disminución de segmentos en estado ACTIVEde 2,9375 MB a 0 MB, esto debido a la instrucción de commit realizada en ese instante.

• 17:55:43, Disminución de segmentos en estado UNEXPIRED de 2,9375 MB a 1,0625 MB y aumento de segmentos en estadoEXPIRED de 10,1875 MB a 12,0625 MB. Esto debido a lo comprometido en el parámetro undo_retention.

Podemos concluir que al gatillar el insert comienzan a ocuparse segmento de undo y quedan en estado activo (consume espacio en tablespace de UNDO), estos segmentos quedan en estado activo hasta que se gatille un commit pasando a quedar estos segmentos en estado UNEXPIRED (se sigue consumiendo espacio en tablespace de UNDO), luego de un tiempo (determinado por el undo_retention) los segmentos en estado UNEXPIRED pasan a estado EXPIRED liberando espacio en tablespace de UNDO.

OJO!, no siempre el tiempo de retención en el UNDO es el definido en parámetro undo_retention, el tiempo de retención es calculado automáticamente por el motor en periodos de 10 minutos, esto se puede verificar en la vista v$undostat, la forma de auto calcular el tiempo de retención es basado en un porcentaje del tamaño de tablaspace UNDO.

Para verificar, la siguiente prueba:

09:38:39 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————-
undo_management                      string      AUTO
undo_retention                       integer     600
undo_tablespace                      string      UNDOTBS1

09:38:46 SQL> alter system set undo_retention=60;

System altered.

09:38:59 SQL>

El undo_retention estaba definido en 900 segundos, se modificó a 60 segundos. Luego para la prueba ejecutaremos un insert:

09:42:33 SQL> @insert.sql

1252775 rows created.

09:42:48 SQL> commit;

Commit complete.

09:43:31 SQL>

Lo esperado es que la retención de segmentos en estado UNEXPIRED no supere los 60 segundos. Un análisis de lo ocurrido con el siguiente grafico:

Como se puede ver en el grafico, los segmentos de UNDO en estado UNEXPIRED se mantuvieron por 600 segundos y no por 60 como se había seteado en undo_retention. La no utilización del tiempo determinado en undo_retention se debe al valor de retención calculado mor el motor y que es posible verificar en la vista v$undostat:

SELECT   begin_time,
         end_time,
         tuned_undoretention
FROM     v$undostat
WHERE    begin_time BETWEEN To_date(’20/05/2009 09:30:00′,‘dd/mm/yyyy hh24:mi:ss’) AND To_date(’20/05/2009 09:55:00′,‘dd/mm/yyyy hh24:mi:ss’)
ORDER BY begin_time DESC

El resultado es:

Como se puede observar, el periodo de undo_retention calculado por el motor es de 600 segundos.

Para reparar esto, es necesario incorporar una variable oculta en el init.ora de la base de datos que inhibe la utilización del TUNED_UNDORETENTION. Es necesario setear en el init.ora el siguiente parámetro:

_undo_autotune = false

O en spfile:

alter system set "_undo_autotune" = false;

Con esta configuración, el TUNED_UNDORETENTION no es actualizado, luego el tiempo de retención de UNDO que se utilizará es el especificado en undo_retention.

Repetiremos el ejemplo:

Seteamos el parámetro:

10:51:00 SQL> Alter system set "_undo_autotune" = false;

System altered.

10:51:06 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS1
10:52:09 SQL>

Se ejecuta un insert:

10:56:12 SQL> @insert.sql

1252775 rows created.

10:56:29 SQL>
10:56:54 SQL> commit;

Commit complete.

10:56:57 SQL>

Lo esperado es que la retención de segmentos en estado UNEXPIRED no supere los 60 segundos. Un análisis de lo ocurrido con el siguiente grafico:

Como se puede ver en el grafico, los segmentos de UNDO en estado UNEXPIRED se mantuvieron por 60 segundos, tal como lo indicado en el parámetro undo_retention.

Transacciones que requieren UNDO

Es posible monitorear que transacciones están ocupando segmentos de UNDO y/o generando bloqueos debido a falta de commit o rollback. Con la siguiente query se puede revisar que transacciones están requiriendo UNDO activamente:

SELECT v$transaction.status AS status_transaccion,
       start_time,
       logon_time,
       blocking_session_status,
       schemaname,
       machine,
       program,
       module,
       v$sqlarea.sql_text,
       serial#,
       sid,
       username,
       v$session.status AS status_sesion,
       sql_id,
       prev_sql_id
FROM   v$transaction
       INNER JOIN v$session
         ON v$transaction.ses_addr = v$session.saddr
       LEFT JOIN v$sqlarea
         ON v$session.sql_id = v$sqlarea.sql_id

Por ejemplo ejecutaremos un insert sin dar commit:

14:56:20 SQL> @insert.sql

4378448 rows created.

15:01:04 SQL>

Al ejecutar la consulta, el resultado es el siguiente (por cuestiones de presentación el resultado se presenta de manera transpuesta):

Donde,

LOGON_TIME, es la fecha y hora en que se ejecutó la instrucción.
BLOCKING_SESSION_STATUS, indica si la sesión está bloqueando a otra sesión.
SCHEMANAME, schema que ejecutó la instrucción.
MACHINE, nombre de la maquina desde donde se está ejecutando la instrucción.
PROGRAM, programa desde donde se ejecuta la instrucción.
MODULE, modulo desde donde se ejecuta la instrucción.
SQL_TEXT, instrucción SQL ejecutada.
SERIAL#, Serial number de la sesión que está ejecutando la instrucción.
SID, sesión Id de la sesión que está ejecutando la instrucción.
USERNAME, usuario que ejecutó la instrucción.
STATUS_SESION, estado de la sesión. ACTIVE si se está ejecutando alguna instrucción, INCATIVE si ya se ejecutó la instrucción y no está realizando ninguna operación.
SQL_ID, ID de la instrucción SQL que se está ejecutando.
PREV_SQL_ID, ID de la instrucción SQL previa a la que se está ejecutando.

Al ejecutar un commit:

16:18:32 SQL> commit;

Commit complete.

16:18:51 SQL>

El resultado es el siguiente:

O sea, no aparecen transacciones activas.

Una o varias transacciones activas que no ejecuten un commit, pueden significar utilización de una gran cantidad de segmentos de UNDO provocando utilización de espacio en tablespace de UNDO.

Calculo UNDO_RETENTION  óptimo

Es posible setear según las estadísticas de la base de datos  los óptimos para el tamaño de tablespace de UNDO y el parámetro undo_retention.

El undo_retention es calculado según el tamaño del tablespace de UNDO, por ejemplo, si contamos con limitaciones de storage y el máximo tamaño del tablespace de UNDO es limitado, es posible calcular el undo_retention optimo para no superar el tamaño de tablespace definido con la siguiente fórmula:

Para realizar este cálculo es necesario contar con las estadísticas de v$undostat, luego para esta prueba es necesario habilitar el undo_autotune de la siguiente forma:

Es necesario setear en el init.ora el siguiente parámetro:

_undo_autotune = true

O en spfile:

alter system set "_undo_autotune" = true;

Además se recomienda que las estadísticas que se toman en v$undostat sean de un tiempo representativo, de este modo no se queda ninguna query fuera del proceso. Por ejemplo, si fue reiniciada la base de datos hace algunos minutos, las estadísticas no serán de mucha ayuda, la idea es que la base de datos esté activa y tomando estadísticas mínimo un día o el tiempo suficiente para que se ejecuten todos los procesos de negocio que  debe soportar el servicio.

Para la prueba, se activará el autotune de la base de datos:

10:11:41 SQL> alter system set "_undo_autotune" = true;

System altered.

10:11:43 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     60
undo_tablespace                      string      UNDOTBS1
10:11:53 SQL>

La siguiente query entrega los datos necesarios para calcular el undo_retention óptimo:

SELECT ‘Tamaño Tablespace UNDO Actual’ AS "Parametro",
       Sum(a.bytes) AS "Valor"
FROM   v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE  c.contents = ‘UNDO’
       AND c.status = ‘ONLINE’
       AND b.NAME = c.tablespace_name
       AND a.ts# = b.ts#
UNION ALL
SELECT ‘Bloques UNDO por segundo’ AS "Parametro",
       Max(undoblks / ((end_time - begin_time) * 3600 * 24)) AS "Valor"
FROM   v$undostat
UNION ALL
SELECT ‘Tamaño Bloque de Base De Datos’ AS "Parametro",
       To_number(VALUE) AS "Valor"
FROM   v$parameter
WHERE  NAME = ‘db_block_size’

El resultado es el siguiente:

O sea, el tamaño del Tablespace de UNDO es 1.3 GB, la cantidad máxima de bloques por segundo es 95 bloques y el tamaño de un bloque es 8 KB. Luego el undo_retention optimo es:

Con la siguiente queda simplificado el cálculo a una sola query:

SELECT d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
       Substr(e.VALUE,1,25) "ACTUAL UNDO RETENTION [Sec]",
       Round((d.undo_size / (To_number(f.VALUE) * g.undo_block_per_sec))) "UNDO RETENTION OPTIMO [Sec]"
FROM   (SELECT Sum(a.bytes) undo_size
        FROM   v$datafile a,
               v$tablespace b,
               dba_tablespaces c
        WHERE  c.contents = ‘UNDO’
               AND c.status = ‘ONLINE’
               AND b.name = c.tablespace_name
               AND a.ts# = b.ts#) d,
       v$parameter e,
       v$parameter f,
       (SELECT Max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
        FROM   v$undostat) g
WHERE  e.name = ‘undo_retention’
       AND f.name = ‘db_block_size’

El resultado de esta es:

Para modificar el undo_retention de la base de datos y no superar en tamaño los 1.3 GB del Tablespace de UNDO es necesario setear el undo_retention a 1831 segundos:

11:50:38 SQL> alter system set undo_retention=1831;

System altered.

11:50:53 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     1831
undo_tablespace                      string      UNDOTBS1
11:50:59 SQL>

Ahora, si no existen problemas de storage, o sea, el Tablespace de UNDO puede crecer aun más y se requiere un tiempo de retención mayor, es necesario calcular el tamaño del tablespace de UNDO óptimo dependiendo del undo_retention que se requiera setear.

Por ejemplo, digamos que se requiere un tiempo de retención igual a 3000 segundos:

12:00:34 SQL> alter system set undo_retention=3000;

System altered.

12:00:46 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     3000
undo_tablespace                      string      UNDOTBS1
12:00:52 SQL>

El tamaño óptimo del tablespace de UNDO para esta retención se da por la siguiente fórmula:

La siguiente query entrega los datos necesarios para calcular el tamaño tablespace UNDO óptimo:

SELECT ‘undo_retention’ AS "Parametro",
       To_number(VALUE) AS "Valor"
FROM   v$parameter
WHERE  NAME = ‘undo_retention’
UNION ALL
SELECT ‘Bloques UNDO por segundo’ AS "Parametro",
       Max(undoblks / ((end_time - begin_time) * 3600 * 24)) AS "Valor"
FROM   v$undostat
UNION ALL
SELECT ‘Tamaño Bloque de Base De Datos’ AS "Parametro",
       To_number(VALUE) AS "Valor"
FROM   v$parameter
WHERE  NAME = ‘db_block_size’

El resultado de esta es la siguiente:

O sea, el undo_retention es 3000 segundos, la cantidad máxima de bloques por segundo es 95 bloques y el tamaño de un bloque es 8 KB. Luego el tamaño de tablespace UNDO optimo es:

Con la siguiente queda simplificado el cálculo a una sola query:

SELECT Substr(e.VALUE,1,25) "UNDO RETENTION [Sec]",
       d.undo_size / (1024 * 1024) "Tamaño Actual UNDO [MByte]",
       (To_number(e.VALUE) * To_number(f.VALUE) * g.undo_block_per_sec) / (1024 * 1024) "Tamaño Optimo UNDO [MByte]"
FROM   (SELECT Sum(a.bytes) undo_size
        FROM   v$datafile a,
               v$tablespace b,
               dba_tablespaces c
        WHERE  c.contents = ‘UNDO’
               AND c.status = ‘ONLINE’
               AND b.NAME = c.tablespace_name
               AND a.ts# = b.ts#) d,
       v$parameter e,
       v$parameter f,
       (SELECT Max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
        FROM   v$undostat) g
WHERE  e.NAME = ‘undo_retention’
       AND f.NAME = ‘db_block_size’

El resultado de esta es:

Luego, solo es necesario modificar el tamaño del datafile asociado al tablespace de UNDO a 2227 MB.

ALTER DATABASE DATAFILE ‘/u02/oradata/jimydb/undotbs01.dbf’ RESIZE 2227M;

Reducir Tamaño de Tablespace de UNDO

Si el tablespace de UNDO tiene los datafiles asociados con la opción autoextend, es posible que crezca indiscriminadamente por alguna razón en particular (una transacción muy grande, un import de alguna tabla grande, etc), luego, en algún momento tal vez se requiera reducir el tablespace de UNDO, lamentablemente, no se puede. La técnica para reducir el tamaño del tablespace de UNDO consiste en crear un nuevo tablespace de tipo UNDO con el tamaño deseado, asignar este tablespace como UNDO predeterminado y eliminar el antiguo.

Lo anteriormente descrito, paso a paso:

El tamaño del tablespace de UNDO obtenido con la siguiente query:

SELECT Sum(v$datafile.bytes / 1024 / 1024) AS "Tamaño Actual TS UNDO [MB]"
FROM   v$datafile
       INNER JOIN v$tablespace
         ON v$datafile.ts# = v$tablespace.ts#
       INNER JOIN dba_tablespaces
         ON v$tablespace.NAME = dba_tablespaces.tablespace_name
WHERE  dba_tablespaces.contents = ‘UNDO’
       AND dba_tablespaces.status = ‘ONLINE’

El resultado es el siguiente:

Supongamos que se requiere reducir el tamaño del tablespace a 1000 MB, la instrucción es la siguiente:

ALTER DATABASE DATAFILE ‘/u02/oradata/jimydb/undotbs01.dbf’ RESIZE 1000M;

Al ejecutar la instrucción aparece el siguiente error:

ORA-03297: file contains used data beyond requested RESIZE value

Bueno, tal vez es muy tajante decir que el tamaño del tablespace no se puede reducir, la verdad si se puede pero solo hasta donde lo permita nuestro bien conocido High Water Mark (HWM). La siguiente query muestra el tamaño actual del tablespace, lo recuperable, el mínimo tamaño que puede tener el tablespace de UNDO, el o  los datafiles a modificar con tamaño propuesto.

SELECT
  tts.tablespace,
  tts."Tamaño TableSpace [MB]",
  tts."Espacio Libre TableSpace [MB]",
  tts."Tamaño TableSpace [MB]"
    - "Espacio Libre TableSpace [MB]" AS "Espacio Propuesto TS [MB]",
  tdf.datafile AS "Datafile a Mofificar",
  tdf."Tamaño DataFile [MB]",
  tdf."Espacio Libre DataFile [MB]",
  tdf."Tamaño DataFile [MB]"
    - tdf."Espacio Libre DataFile [MB]" AS "Tamaño Propuesto DF [MB]"
FROM
  (SELECT
  tablespace,
 Sum("Tamaño DataFile [MB]") AS "Tamaño TableSpace [MB]",
 Sum("Espacio Libre DataFile [MB]") AS "Espacio Libre TableSpace [MB]"
   FROM
    (SELECT
      dfs.tablespace_name AS tablespace,
      hwm.bytes
        / 1024
        / 1024 AS "Tamaño DataFile [MB]",
      Sum(dfs.bytes)
        / 1024
        / 1024 AS "Espacio Libre DataFile [MB]"
     FROM
      dba_free_space dfs
      INNER JOIN (SELECT
                   df.file_id,
                   Max(block_id) maximum,
                   df.bytes AS bytes
                  FROM
                   dba_extents ext
                   INNER JOIN dba_data_files df
                     ON df.file_id = ext.file_id
                  GROUP BY df.file_id,
                           df.bytes) hwm
        ON dfs.file_id = hwm.file_id
      INNER JOIN dba_tablespaces dts
        ON dfs.tablespace_name = dts.tablespace_name
           AND dts.contents = ‘UNDO’
           AND dts.status = ‘ONLINE’
     GROUP BY hwm.maximum,
              dfs.tablespace_name,
              hwm.bytes
     HAVING   Max(block_id) >= hwm.maximum)
   GROUP BY tablespace) tts
  INNER JOIN (SELECT
               dfs.tablespace_name AS tablespace,
               hwm.file_name AS datafile,
               hwm.bytes
                 / 1024
                 / 1024 AS "Tamaño DataFile [MB]",
               Sum(dfs.bytes)
                 / 1024
                 / 1024 AS "Espacio Libre DataFile [MB]",
               dfs.file_id,
               Max(block_id) AS block_id
              FROM
               dba_free_space dfs
               INNER JOIN (SELECT
                            df.file_id,
                            df.file_name,
                            Max(block_id) maximum,
                            df.bytes AS bytes
                           FROM
                            dba_extents ext
                            INNER JOIN dba_data_files df
                              ON df.file_id = ext.file_id
                           GROUP BY df.file_id,
                                    df.file_name,
                                    df.bytes) hwm
                 ON dfs.file_id = hwm.file_id
               INNER JOIN dba_tablespaces dts
                 ON dfs.tablespace_name = dts.tablespace_name
                    AND dts.contents = ‘UNDO’
                    AND dts.status = ‘ONLINE’
              GROUP BY dfs.file_id,
                       hwm.file_name,
                       hwm.maximum,
                       dfs.tablespace_name,
                       hwm.bytes
              HAVING   Max(block_id) >= hwm.maximum
              ORDER BY dfs.tablespace_name,
                       hwm.file_name) tdf
    ON tts.tablespace = tdf.tablespace
ORDER BY tts.tablespace,
         tdf.datafile

El resultado de esta:

Luego, según el resultado de la query, se puede reducir el tamaño del tablespace (datafile asociado) a 1361 MB:

14:43:12 SQL> ALTER DATABASE DATAFILE ‘/u02/oradata/jimydb/undotbs01.dbf’ RESIZE 1361M;

Database altered.

14:43:18 SQL>

Ningún Problema!

En caso de que se requiera modificar el tamaño del tablespace de UNDO a un valor menor al permitido por la HWM es necesario aplicar la técnica comentada al principio. Para este ejemplo, dejaremos el tablespace de UNDO en 1000 MB.

Primero se debe crear un nuevo tablespace de UNDO:

CREATE UNDO TABLESPACE "UNDOTBS_TEMP" DATAFILE ‘/u02/oradata/jimydb/undotbs_temp.dbf’ SIZE 1000M;

Luego se debe establecer el nuevo tablespace como tablespace de UNDO  predeterminado:

ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS_TEMP’ scope=both;

Ahora la base de datos cuenta con nuevo tablespace de UNDO y del tamaño requerido, luego, es posible eliminar el antiguo tablespace de UNDO

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Ejemplo:

17:38:35 SQL> CREATE UNDO TABLESPACE "UNDOTBS_TEMP" DATAFILE ‘/u02/oradata/jimydb/undotbs_temp.dbf’ SIZE 1000M;

Tablespace created.

17:39:05 SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS_TEMP’ SCOPE=BOTH;

System altered.

17:39:38 SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

17:39:46 SQL>
17:39:48 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     3000
undo_tablespace                      string      UNDOTBS_TEMP
17:40:50 SQL>

¡Excelente!, notar que se nombro el Nuevo tablespace como UNDOTBS_TEMP dado que se podría desear que el tablespace de UNDO mantenga el mismo nombre del inicio, para eso habría que repetir los pasos anteriores, ejemplo:

17:44:18 SQL> CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE ‘/u02/oradata/jimydb/undotbs01.dbf’ SIZE 1000M;

Tablespace created.

17:44:47 SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS1′ SCOPE=BOTH;

System altered.

17:45:16 SQL> DROP TABLESPACE UNDOTBS_TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

17:45:22 SQL> sho parameters undo

NAME                                 TYPE        VALUE
———————————— ———– —————————–
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     3000
undo_tablespace                      string      UNDOTBS1
17:45:29 SQL>

¡Listo!, ahora está la base de datos igual que antes y el tablespace de UNDO sigue siendo el mismo pero con un tamaño reducido.

Tal vez genere errores al intentar eliminar el tablespace antiguo (en el primer ejemplo), esto se debe a que existen segmentos activos, para ello es necesario identificar los segmentos que no están OFFLINE:

SELECT segment_name,
       tablespace_name,
       status
FROM   dba_rollback_segs
WHERE  tablespace_name = ‘UNDOTBS1′

El resultado es:

Todos los segmentos que no están OFFLINE es necesario forzar su estado, para ello se debe modificar el init.ora con el siguiente parámetro:

*._offline_rollback_segments=("_SYSSMU3$","_SYSSMU5$")

Se baja la base de datos y se sube con archivo init.ora, luego se borran los segmentos conflictivos:

DROP ROLLBACK SEGMENT ‘_SYSSMU3$’;
DROP ROLLBACK SEGMENT ‘_SYSSMU5$’;

Se omite el error que se arrojará y se procede a borrar el tablespace:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;