Alteração da tablespace de UNDO no banco de dados Oracle

Este artigo apresentará os passos para a alteração na tablespace de UNDO no banco de dados Oracle.

Como alterar o banco de dados para uma nova tablespace UNDO e descartar a tablespace antiga.

Primeiro vamos verificar os parâmetros ativos no banco de dados:

$ sqlplus / as sysdba

SQL> show parameter undo NAME                      TYPE        VALUE
------------------------- ----------- ------------------------------
UNDO_MANAGEMENT           string      AUTO
UNDO_RETENTION            integer     600
UNDO_TABLESPACE           string      UNDOTBS1

A tablespace de UNDO atual como sugerido pela inicialização parâmetro UNDO_TABLESPACE é UNDOTBS1, que é a tablespace que armazena os segmentos de UNDO, o gerenciamento está configurado como automático (UNDO_MANAGEMENT) e possui uma retenção de 600 segundos (UNDO_RETENTION). Para realizarmos um teste vamos abriremos outra sessão (SCOTT) para simularmos uma transação no banco de dados.

$ sqlplus scott/tiger

SQL> update emp set sal = sal + 1500 where empno=7839;

1 row updated.

Quando realizamos um “update” na tabela EMP, iniciamos uma transação, os dados são escritos inicialmente em um segmento de UNDO, na tablespace UNDOTBS1, para depois serem gravados na tabela EMP.

Essa sessão ficará propositalmente aberta, sem a finalização da transação (COMMIT ou ROLLBACK).

Iremos criar uma nova tablespace de UNDO, chamada de UNDOTBS2.
 
SQL> CREATE UNDO TABLESPACE undotbs2
DATAFILE '/apps/oradata/dbf/undotbs02.dbf'
         SIZE 50M AUTOEXTEND OFF NEXT 5M;
 
Tablespace created.
 
Vamos alterar o banco de dados para a nova tablespace de UNDO (UNDOTBS2).
 
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
 
System altered.
 
Vamos tentar remover a tablespace de UNDO antiga  (UNDOTBS1).
 
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
 
Com a nova tablespace de UNDO criada (UNDOTBS2), a tablespace UNDO já foi alterada no banco de dados. A partir do momento da criação da nova tablespace de UNDO todas as novas transações passarão a utilizar os segmentos de UNDO que estão criados na nova tablespace, ou seja, UNDOTBS2.
 
Porém os dados de transações pendentes em nosso exemplo pela conexão do usuário (SCOTT) ainda estão apontando para o segmento de UNDO antigo e possuem o “status” pendente OFFLINE.  

Abaixo segue o resultado de uma consulta nos segmentos de UNDO.
 
column username format a6
 
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         151          2
 
A sessão que está com a operação pendente é a conexão realizada no inicio da demonstração, com o usuário SCOTT (SID = 151 e SERIAL# = 2). Neste exemplo vamos eliminar a sessão no banco de dados, porém, poderíamos realizar um (COMMIT ou ROLLBACK) para finalizarmos a transação normalmente.
 
O objetivo de eliminarmos a sessão é para verificarmos o tempo de retenção do segmento de UNDO, que está configurado no banco de dados em 600 segundos.
 
SQL> alter system kill session '151, 2' immediate;
 
System altered.
 
 
Depois da sessão eliminada, foi realizada uma nova consulta para verificar o “status” do segmento de UNDO.
 
SQL> 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 (
              WHERE tablespace_name = 'UNDOTBS1'
             );
 
no rows selected

Como podemos verificar depois que a sessão foi eliminada e não vemos mais os segmentos ocupados na tablespace UNDOTBS1. Iremos remover a tablespace UNDOTBS1, pois todos os segmentos de UNDO ativos encontram-se na nova tablespace UNDOTBS2.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

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

Ao executar o comando acima, a mensagem de erro foi apresentada (ORA-30013). Essa mensagem é referente ao tempo de retenção estabelecido pelo banco de dados (parâmetro UNDO_RETENTION), que está configurado em 600 segundos. Parâmetro deve sempre ser configurado em segundos, neste exemplo, 600 segundos, ou seja, 10 minutos.

Após 10 minutos o comando foi re-executado, permitindo a remoção da tablespace UNDOTBS1.

SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

MSc. Rubens Oliveira

DBA Oracle Consultor

olivert.dba@consultant.com