Tablespace temporária muito grande

Não sei se isto ocorre no 10g (mas vou saber em breve), mas com o 9i, os tablespaces temporários tem o péssimo hábito de crescer e não diminuir depois. O fato é que se você não ficar de olho, chegará o dia em que o tablespace temporário estará enorme e cheio recebendo mensagens de erro de aplicações que não conseguem extender o tablespace temporário e ficando com os discos cheios sem necessidade. Não há como diminuir o tablespace (é feio e potenciamente desastroso diminuir o tamanho de tablespaces, mas todo mundo faz isso um dia…) pois todo o seu espaço está ocupado. Qual a solução? Criar um novo tablespace temporário e remover o anterior.

Antes de mais nada, vale a pena dar uma olhada no tamanho dos seus arquivos no tablespace temporário olhando em dba_temp_files:

SELECT
  tablespace_name,
  file_name,
  bytes/(1024*1024) tamanho_MB,
  maxbytes/(1024*1024) maximo_MB,
  status
  FROM dba_temp_files
  ORDER BY tablespace_name, file_name;

Você deve sempre criar o seu tablespace temporário com arquivos com limite de tamanho. Se o tamanho máximo estiver próximo do total ocupado, é hora de recriar o seu tablespace:

CREATE TEMPORARY TABLESPACE temporaria TEMPFILE
  '/oradata/PD02/db/temporaria_01.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 2000M,
  '/oradata/PD02/db/temporaria_02.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 2000M,
  '/oradata/PD02/db/temporaria_03.dbf' SIZE 50M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 2000M
  EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temporaria;

Após criar o novo tablespace temporário (neste caso um o tablespace ‘temporaria’ com 3 arquivos de 50MB que podem chegar a 2GB cada) e torna-lo padrão, é preciso remover o tablespace temporário antigo. Mas para isso, devemos checar se não existe nenhuma transação em execução utilizando a tablespace antiga. Se houver, devemos esperar as transações terminarem ou abortar a sua execussão. Rode a consulta a seguir para verificar se há alguma transação em execução e qual o tablespace temporário utilizado:

SELECT
  su.username,
  s.osuser,
  su.tablespace,
  TO_CHAR (s.logon_time, 'dd/mm/yyyy hh:mi:ss') logon,
  s.sid, s.serial#,
  s.machine,
  s.terminal,
  s.program
  FROM
    v$sort_usage su JOIN
      v$session s ON su.session_addr = s.saddr;

Se não houver ninguém utilizando o tablespace temporario antigo, então você pode apaga-lo:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;