 |
|
ORA-01653 Tips
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 error:
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
space availability:
Explanation:
------------
This ORA-01653 error does not necessarily indicate whether or not you have enough
space
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.
Diagnostic Steps:
-----------------
1. In order to see the free space available for a particular tablespace, you
must
use the view DBA_FREE_SPACE. Within this view, each record represents
one
fragment of space. How the view DBA_FREE_SPACE can be used to
determine
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
Extents
- Add a
DATAFILE
- Resize
DATAFILE
- Enable
AUTOEXTEND
- Defragment tablespace
- View temporary segments for space
