[oracle] Tablespace de UNDO muito grande

[oracle] Tablespace de UNDO muito grande

8012010

Ontem tive um problema com um banco de dados, o banco não conseguia estender mais espaço para a tablespace de UNDO, pois, a mesma havia chego no seu tamanho máximo.

Mesmo parando todas as sessões do BD a tablespace não diminuia, então pesquisando um pouco verifiquei que a tablespace de UNDO possui um tempo de retenção de dados, na qual é utilizado para busca de informações do passado (informações já commitadas),  como a funcionalidade de flashback do Oracle.

Como verificar o melhor tempo de retenção

Você pode determinar o período de retenção por meio de consulta a coluna TUNED_UNDORETENTION da visão V $ UNDOSTAT. Esta exibição contém uma linha para cada 10 minutos de intervalo de coleta de estatísticas, nos últimos 4 dias (TUNED_UNDORETENTION é dado em segundos).

select
     to_char(begin_time, 'DD-MON-RR HH24:MI')
     begin_time,
     to_char(end_time, 'DD-MON-RR HH24:MI')
     end_time, tuned_undoretention
 from v$undostat
 order by end_time;

BEGIN_TIME	END_TIME	TUNED_UNDORETENTION
08-JAN-10 09:15	08-JAN-10 09:25	600
08-JAN-10 09:25	08-JAN-10 09:35	600
08-JAN-10 09:35	08-JAN-10 09:45	600
08-JAN-10 09:45	08-JAN-10 09:55	600
08-JAN-10 09:55	08-JAN-10 10:05	600

Especificando o tempo de retenção

Para especificar o tempo de retenção pelo atributo você pode mudar o atributoUNDO_RETENTION no arquivo de parâmetros de inicialização do BD.

Para mudar o tempo de retenção a qualquer momento basta executar o seguinte comando:

ALTER SYSTEM SET UNDO_RETENTION = 600;

A mudança ocorrerá imediatamente, mas só será efetivamente aplicada se a tablespace de UNDO possuir espaço sufiente.

Limpando os dados da tablespace de UNDO

No problema  (tablespace de UNDO estava muito grande)  havia um agravante de  limitação de espaço, uma vez que este cliente estava utilizando o Oracle XE.

Então foi necessário limpar a tablespace de UNDO, pesquisando um pouco descobri que isso só é possível criando uma nova tablespace de UNDO e apagando a velha com seus dados. Para efetuar esse processo é necessário executar os seguintes comandos:

-- Cria uma nova tablespace
create undo tablespace undotbs02 datafile 'C:\ORACLEXE\ORADATA\XE\UNDO02.DBF'
size 400M autoextend on next 50m maxsize 700M;

-- especifica para o banco de dados a nova tablespace de UNDO
alter system set undo_tablespace='undotbs02';

-- apaga a antiga tablespace de UNDO
drop tablespace UNDO including contents and datafiles;

Referencia:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/undo.htm
https://ldiasrs.wordpress.com/2010/01/08/oracle-tablespace-de-undo-muito-grande/