English

Error: ORA-01652: unable to extend temp segment

Solution or Workaround

Description

When trying to load feature classes from an ArcSDE export file into ArcSDE for Oracle, an error occurred:

ArcSDE 8.0.1
Layer Administration Utility
-----------------------------------------------------
Error: Underlying DBMS error (-51).
Error: Setting Layer to NORMAL I/O mode.
ORA-01652: unable to extend temp segment by 5 in tablespace SPATIAL
(SPATIAL.S14_IX2)
Layer is still in LOAD-ONLY mode!

The Spatial tablespace and TEMP tablespaces both are set to unlimited.

Solution

Even if the MAXEXTENTS is set to UNLIMITED, there may not be any room in the SPATIAL tablespace. Check two things: if the data files are set to "autoextend", if not, check if there is enough free space:

SELECT file_name, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'SPATIAL';

If AUTOEXTENSIBLE is YES for any of the data files, check to see if there is enough space on the drive. If it is NO for all of the data files, see if there is enough space in the tablespace:

ALTER TABLESPACE spatial COALESCE;

SELECT sum(bytes), max(bytes), count(*)
FROM dba_free_space
WHERE tablespace_name = 'SPATIAL';

In this output, sum(bytes) is the total amount of free space there is in SPATIAL, max(bytes) is the size of the biggest free extent, and count(*) is the total number of free extents there is. Coalescing the free space before running this gives a correct indicator of how big the biggest free extent really is. If a lot of work was done before Oracle's SMON has a chance to coalesce space, it can get a false small value for the size of the biggest free extent. Oracle will try to coalesce the data if it cannot allocate an extent because all of the free extents are too small.

The biggest free extent (the middle column) must be big enough to hold the amount of space that is trying to be allocated.

If there is not enough space to coalesce, add another data file to the SPATIAL tablespace.