Oracle tablespace full: how to fix ORA-01652, ORA-01653 and ORA-30036

ORA-01652 usually arrives truncated, with nothing but the number. The full message is the part that tells you where to grow.

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

The tablespace name comes at the end of the message. That is the one you grow, and every Oracle space error carries that name in the same position.

A permanent tablespace fills up under normal use. It takes no heavy load, no import and no special operation: everyday inserts keep taking up the datafiles until they reach the ceiling. When that happens, the error names the table or the index that could not grow.

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

ORA-01652 also shows up on a permanent tablespace, and that is where it gets confusing, because the message mentions a temp segment.

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

This happens with CREATE INDEX, CREATE TABLE AS SELECT and ALTER TABLE MOVE. Oracle builds a temporary segment inside the target tablespace and only turns it into the final object at the end. TEMP has nothing to do with the problem, and growing TEMP changes nothing.

The other two space errors are UNDO and the partition.

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

In ORA-01688 the tablespace in the error is the partition’s, which is not always the same one as the table’s.

The alert.log records the same event, with a timestamp, but without the leading zero. Searching the file for ORA-01652 returns nothing.

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

How much room the tablespace still has

What decides whether a tablespace has reached the end is not the free space, it is the autoextend ceiling. A tablespace with 2 MB free and autoextend up to 32 GB still has somewhere to go. One with 4 GB free and autoextend turned off does not.

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);

When mb_teto comes back equal to mb_hoje, autoextend is off or has already hit MAXSIZE.

The tablespace datafiles show which one to work on and where it is stored.

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;

The space on the mount point has to be checked first. ADD DATAFILE on a full filesystem fails halfway through.

df -h /u02
asmcmd lsdg

Growing the tablespace

Enlarging the datafile that already exists is the shortest path.

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

With autoextend on, the database grows on its own next time around. MAXSIZE is mandatory: on UNLIMITED the datafile grows until the filesystem runs out, and at that point the problem stops being one application’s and becomes the server’s.

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

When the datafile is already at its limit, another one comes in.

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

On a smallfile tablespace with an 8 KB block, the datafile stops at 32 GB. Past that, RESIZE returns a different error.

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

In an environment that grows a lot, a bigfile tablespace has a single datafile and does not run into that ceiling.

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

If the database will not accept any more datafiles, this is the error. The way out is to raise the db_files parameter, which requires a restart.

ORA-00059: maximum number of DB_FILES exceeded

TEMP and UNDO

TEMP does not take a datafile, it takes a tempfile. The commands look alike, the word changes.

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;

To give back what TEMP has grown into, SHRINK releases only what is genuinely free. With the sessions still active it gives nothing back, and it does not complain either.

ALTER TABLESPACE temp SHRINK SPACE KEEP 8G;

UNDO grows like an ordinary tablespace, with a datafile.

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

ORA-30036 comes with a catch. Growing UNDO resolves it, but when the error keeps coming back at the same time of day, it is usually a purge job deleting millions of rows in a single DELETE, with no commit along the way. Until that transaction closes, its undo is not reused, no matter how large the tablespace gets.

After growing it, the first query confirms the result: mb_teto has to have gone up.

For how Oracle organizes that space internally, there is the article on Oracle storage structures. If you would like us to take care of this in your environment, just talk to us.