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:

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:
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:
Se não houver ninguém utilizando o tablespace temporario antigo, então você pode apaga-lo: