ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 Unable to Extend

A typical error is,

SQL> create table test2 tablespace data as select level a from dual connect by level <99999999;create table test2 tablespace data as select level a from dual connect by level <99999999*ERROR at line 1:ORA-01652: unable to extend temp segment by 128 in tablespace DATA

Cause of the Error:
---------------------
Whenever the error comes "unable to extend" it means there is insufficient contiguous space in the specified segment of the tablespace.

Troubleshooting of the Problem:
------------------------------------
The error "unable to extend" result as it could not find contiguous blocks. The largest contiguous space available for the tablespace can be shown by the query,

SQL>SELECT max(bytes)/1024/1024 "Space in MB" FROM dba_free_space
WHERE tablespace_name = '&tablespace_name';
Space in MB
-----------
1.75

Here contiguous block size is 1.75 MB which is not sufficient to perform insert operation as above. So error results.

Possible Solutions
----------------------
A)Manually Coalesce Adjacent Free Extents:
-----------------------------------------------
ALTER TABLESPACE &tablespace_name COALESCE;

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

B)Adding a Datafile to the Specified Tablespace.
------------------------------------------------------------------
ALTER TABLESPACE &tablespace_name
ADD DATAFILE 'full path and file name' SIZE value ;

Like,

SQL> ALTER TABLESPACE TEST_DF_DELETE ADD DATAFILE '/oradata2/arju/new2.dbf' size 100M autoextend on;
C) Resize the Datafile to a Larger one.
-------------------------------------------
ALTER DATABASE DATAFILE 'full path and file name'
RESIZE value ;