Oracle: Truncar tabela grande leva uma eternidade

Presumo que você esteja usando espaços de tabela gerenciados por dicionário. Você tem que esperar até que todas as extensões sejam desalocadas. A instrução a seguir deve relatar o número de extensões que devem ser desalocadas:

select extents 
  from dba_segments 
  where owner='TABLEOWNER' 
  and segment_name='TABLENAME' 

Segmentos de índice e logs de visualização materializada também devem ser desalocados, se existirem.

Se você descartar e recriar a tabela, também terá que esperar. A eliminação da tabela levará tanto tempo quanto o truncamento da tabela porque o número de extensões a liberar é quase o mesmo.

Talvez o seguinte ajude: em vez de largar e recriar você

1) renomeie a tabela TABLENAME para OLDTABLE
2) crie uma nova tabela com o nome original TABLENAME. Agora você tem uma tabela vazia TABLENAME
3) você descarta a tabela OLDTABLE

A vantagem dessa abordagem é que você pode continuar seu trabalho com a tabela TABLENAME após a etapa 2. Mas o espaço ocupado pela tabela anterior TABLENAME será liberado durante a execução da etapa 3. No Linux, você pode iniciar o trabalho que faz a etapa 3 no background com nohup para que você não deixe a janela do terminal aberta. Use nohup sqlplus user/password@tnsname @dropscript.sql &.

Mesmo que você queira reiniciar o banco de dados durante a execução da etapa 3, isso não é um problema porque o Oracle pode lidar com essas situações. Pelo que me lembro, ele faz isso da seguinte maneira: Após o reinício da instância (ou se a sessão que faz a queda da tabela morrer por algum outro motivo), o segmento da tabela é convertido em segmentos temporários. Suas extensões são desalocadas por um processo em segundo plano (acho que é smon). A tabela é removida do dicionário após o término da sessão, portanto, o nome da tabela OLDTABLE não está mais visível no dicionário.

O que finalmente resolveu meu problema foi desativar o modo de arquivamento , executar o truncamento e reativar o modo de arquivamento.

Com o modo de arquivamento ativado, o truncamento demorava muito e corrompia o banco de dados.