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.