“ORA-01653 error Tablespace full “

Tablespace is a logical entity in oracle database which stores the objects and metadatas.

If tablespace is full ,then the upcoming files have no room to store the data.

[oracle@orcl ~]$ oerr ora 01653
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.

Solutions:

Here USERS tablespace has very low space.

1)add a datafile to the tablespace or resize the tablespace

check the space of the tablespace for which you get error and add a datafile to the tablespace!

SELECT TABLESPACE_NAME,SUM(BYTES)/1073741824 "FREE_SPACE(GB)" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME                FREE SPACE(GB)
------------------------------ --------------
SYSAUX                             .052734375
UNDOTBS1                           .011657715
USERS                              .003234863    <------
SYSTEM                             .332702637
EXAMPLE                            .024108887

Check the datafile location on the disk

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
+DATA/ORCL/DATAFILE/users.258.1031501899
USERS


you can add a datafile if the extent size reached max for tablespace of 32G
If not, then resize the tablespace to high value if autoextend is off

SQL> alter database datafile '+DATA/ORCL/DATAFILE/users.258.1031501899' RESIZE 500M;

Database altered.

SQL> alter tablespace USERS add datafile '+DATA' size 50M;

Tablespace altered.