Oracle Error Tips by Burleson Consulting (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 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