Tablespace cheia no Oracle: como resolver ORA-01652, ORA-01653 e ORA-30036

O ORA-01652 costuma chegar cortado, só com o número. A mensagem completa é o que diz onde crescer.

ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

O nome da tablespace vem no final da mensagem. É nela que se cresce, e todos os erros de espaço do Oracle trazem esse nome na mesma posição.

Uma tablespace permanente estoura com uso normal. Não precisa de carga grande, importação nem operação especial: o insert do dia a dia vai ocupando os datafiles até chegar no teto. Quando isso acontece, o erro nomeia a tabela ou o índice que não conseguiu crescer.

ORA-01653: unable to extend table SCOTT.VENDAS by 8192 in tablespace USERS

ORA-01654: unable to extend index SCOTT.IDX_VENDAS_DT by 1024 in tablespace USERS

O ORA-01652 também aparece em tablespace permanente, e aí confunde, porque a mensagem fala em temp segment.

ERROR at line 1:
ORA-01652: unable to extend temp segment by 1024 in tablespace USERS

Isso acontece em CREATE INDEX, CREATE TABLE AS SELECT e ALTER TABLE MOVE. O Oracle monta um segmento temporário dentro da tablespace de destino e só converte em objeto definitivo no final. A TEMP não tem nada a ver com o problema, e crescer a TEMP não muda nada.

Os outros dois erros de espaço são a UNDO e a partição.

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

ORA-01688: unable to extend table SCOTT.VENDAS partition P2024 by 8192 in tablespace TS_2024

No ORA-01688 a tablespace do erro é a da partição, que nem sempre é a mesma da tabela.

O alert.log registra a mesma ocorrência, com hora, mas sem o zero à esquerda. Procurando por ORA-01652 no arquivo não aparece nada.

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Quanto a tablespace ainda pode crescer

O que decide se a tablespace está no fim não é o espaço livre, é o teto do autoextend. Uma tablespace com 2 MB livres e autoextend até 32 GB tem para onde ir. Uma com 4 GB livres e autoextend desligado, não.

SELECT t.tablespace_name,
       ROUND(SUM(d.bytes)/1024/1024)                    mb_hoje,
       ROUND(SUM(CASE WHEN d.autoextensible = 'YES'
                      THEN d.maxbytes ELSE d.bytes END)/1024/1024) mb_teto,
       ROUND(NVL(f.free_bytes,0)/1024/1024)             mb_livre_dentro,
       COUNT(*)                                         datafiles,
       MIN(d.autoextensible)                            autoextend
FROM   dba_tablespaces t
       JOIN dba_data_files d
         ON d.tablespace_name = t.tablespace_name
       LEFT JOIN (SELECT tablespace_name, SUM(bytes) free_bytes
                  FROM   dba_free_space
                  GROUP  BY tablespace_name) f
         ON f.tablespace_name = t.tablespace_name
GROUP  BY t.tablespace_name, f.free_bytes
ORDER  BY SUM(CASE WHEN d.autoextensible = 'YES'
                   THEN d.maxbytes ELSE d.bytes END)
          - SUM(d.bytes) + NVL(f.free_bytes,0);

Quando o mb_teto vem igual ao mb_hoje, o autoextend está desligado ou já bateu no MAXSIZE.

Os datafiles da tablespace mostram em qual mexer e onde ele está gravado.

SELECT file_id, file_name,
       ROUND(bytes/1024/1024)    mb,
       ROUND(maxbytes/1024/1024) mb_max,
       autoextensible
FROM   dba_data_files
WHERE  tablespace_name = 'USERS'
ORDER  BY file_id;

O espaço no ponto de montagem tem que ser conferido antes. ADD DATAFILE em filesystem cheio falha no meio da execução.

df -h /u02
asmcmd lsdg

Crescendo a tablespace

Aumentar o datafile que já existe é o caminho mais curto.

ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' RESIZE 32G;

Com o autoextend ligado, o banco cresce sozinho na próxima vez. O MAXSIZE é obrigatório: em UNLIMITED o datafile cresce até acabar o filesystem, e aí o problema deixa de ser de uma aplicação e passa a ser do servidor.

ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf'
      AUTOEXTEND ON NEXT 512M MAXSIZE 48G;

Quando o datafile já está no limite, entra outro.

ALTER TABLESPACE users ADD DATAFILE '/u02/oradata/ORCL/users02.dbf'
      SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 48G;

Numa tablespace smallfile com bloco de 8 KB o datafile para em 32 GB. Passando disso, o RESIZE devolve outro erro.

ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks

Em ambiente que cresce muito, a bigfile tem um datafile só e não esbarra nesse teto.

CREATE BIGFILE TABLESPACE dados_big
       DATAFILE '/u02/oradata/ORCL/dados_big01.dbf'
       SIZE 50G AUTOEXTEND ON NEXT 1G MAXSIZE 2T;

Se o banco não aceitar mais datafile nenhum, o erro é este. O caminho é subir o db_files no parâmetro, o que exige reinício.

ORA-00059: maximum number of DB_FILES exceeded

TEMP e UNDO

A TEMP não recebe datafile, recebe tempfile. Os comandos são parecidos, a palavra muda.

ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/ORCL/temp02.dbf'
      SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

ALTER DATABASE TEMPFILE '/u02/oradata/ORCL/temp01.dbf' RESIZE 16G;

Para devolver o que a TEMP inchou, o SHRINK libera só o que estiver realmente livre. Com as sessões ainda ativas ele não devolve nada e também não reclama.

ALTER TABLESPACE temp SHRINK SPACE KEEP 8G;

A UNDO cresce como tablespace comum, com datafile.

ALTER TABLESPACE undotbs1 ADD DATAFILE '/u02/oradata/ORCL/undotbs02.dbf'
      SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

No ORA-30036 tem um detalhe. Crescer a UNDO resolve, mas quando o erro volta sempre no mesmo horário costuma ser um expurgo apagando milhões de linhas num DELETE só, sem commit no meio. Enquanto a transação não fecha, o undo dela não é reaproveitado, por maior que a tablespace fique.

Depois de crescer, a primeira consulta confirma: o mb_teto tem que ter subido.

Sobre como o Oracle organiza esse espaço por dentro, tem o artigo de estruturas de armazenamento do Oracle. Se quiser que a gente cuide disso no seu ambiente, é só falar com a gente.