Oracle Error Tips by Donald Burleson(S. Karam)
Question: I am getting an ORA-01653 error. What
is the fix fir the ORA-01653 error?
Answer: The ORA-01653 error is caused because you
need to add space to a tablespace.
The Oracle docs note this on the
ORA-01653: unable to extend table string. string by
string in tablespace string
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.
Oracle offer great resources
on resolving ORA-01653, namely the nature of the problem as it relates to
This ORA-01653 error does not necessarily indicate whether or not you have enough
in the tablespace, it merely indicates that Oracle could not find a large
enough area of free
contiguous space in which to fit the next extent.
1. In order to see the free space available for a particular tablespace, you
use the view DBA_FREE_SPACE. Within this view, each record represents
fragment of space. How the view DBA_FREE_SPACE can be used to
the space available in the database is described in
Note 121259.1 Using DBA_FREE_SPACE
Furthermore, we see these choices in
resolving ORA-01653 (as quoted below):
- Manually COALESCE Adjacent Free
- Add a
- Defragment tablespace
- View temporary segments for space