Question

ORA-1653: unable to extend table EMP by 4204142 in tablespace USERS

Answer 1

ORA 1653 ERROR OCCURS WHEN THERE IS NO SPACE FOR THE EXTENT TO INCREASE IN THE TABLE SPACE

SO FOR THAT WE HAVE TO CHECK MAINLY SPACE IN THE TABLE SPACE FROM THE VIEW DBA_FREE_SPACE

 

POSSIBLE SOLUTIONS

Manually Coalesce Adjacent Free Extents

ALTER TABLESPACE COALESCE; 

The extents must be adjacent to each other for this to work

OR

we can add a data file

ALTER TABLESPACE ADD DATAFILE 'SIZE' ;

OR

we can resize the data file

ALTER DATABASE DATAFILE 'RESIZE';

OR

we can directly set to AUTO EXTEND ON

ALTER DATABASE DATAFILE ?? AUTOEXTEND ON 
MAXSIZE UNLIMITED;