ORA-01652 Temp Segment and SDE Load

Here is an interesting error I came across while exporting a relatively big
feature class (30 GB in size with 15 million records) from ArcSDE:-

H:\>sdeexport -o create -l HUGE_FC,SHAPE -a all -f C:\BIG_FC_EXP -i 5151 -u user1 -p user1 ArcSDE 9.2 for Oracle10g Build 1081 Sun Sep 17 16:01:22 2006 SDEX File Export Administration Utility ----------------------------------------------------- Exporting ArcSDE object to "C:\BIG_FC_EXP" in SDEX export format ... Exporting table "HUGE_FC". Spatial column "SHAPE" SDE Code (-51): Underlying DBMS error Extended DBMS error code: 0 Error in exporting SDE feature/attribute data. Error exporting SDE export file. sdeexport error. 1 features converted. 0 features exported.

The above did not have much information and here is the error message in sde_***.log
in the ArcSDE Home\etc folder:-

[Tue Apr 14 12:20:19 2009] [199848] [GISDB] db_array_fetch_attrs OCI Fetch Error (1652) [Tue Apr 14 12:20:19 2009] [199848] [GISDB] load_buffer error -51

Here is the message from Oracle's alert.log :-

Tue Apr 14 12:20:19 2009 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

BINGO, I found out the exact cause and now I know how to fix it. Look at the error number in the SDE Log and Alert log, its matches, but the error in SDE Log has no additional information to troubleshoot the problem.

But before I fix it, let me see what it does when I run this:-

H:\>sde2shp -o init -l HUGE_FC,SHAPE -f C:\BIG_SHPFILE -a all -t arc -i 5151 -u user1 -p user1 ArcSDE 9.2 for Oracle10g Build 1081 Sun Sep 17 16:01:22 2006 Layer Features to Shape File Administration Utility ----------------------------------------------------- C:\BIG_SHPFILE.shp is not a legal shapefile name (not 8.3) SDE Code (-51)= Underlying DBMS error Extended DBMS error code: 1652 ORA-01652: unable to extend temp segment by 128 in tablespace TEMP Error in stream fetch.

See what this one has thrown!, now the error message has more meaning! Here
is the fix:-

SQL> select file_name,bytes/1024/1024/1024 "GB", maxbytes/1024/1024/1024 "MAXGB" from dba_temp_files; FILE_NAME GB MAXGB ------------------------------------ ---------- ---------- E:\ORACLE\ORADATA\GISDB\TEMP01.DBF 10 10

Found that the TEMP tablespace is full, so increased its capacity by increasing the max size limit and added a new data file:-

SQL> alter database tempfile 'E:\ORACLE\ORADATA\GISDB\TEMP01.DBF' AUTOEXTEND on maxsize 20g; Database altered. SQL> alter TABLESPACE TEMP add tempfile 'E:\ORACLE\ORADATA\GISDB\TEMP02.DBF' > SIZE 1024M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 20G; Tablespace altered. SQL> select file_name,bytes/1024/1024/1024 "GB", maxbytes/1024/1024/1024 "MAXGB" from dba_temp_files; FILE_NAME GB MAXGB ------------------------------------ ---------- ---------- E:\ORACLE\ORADATA\GISDB\TEMP01.DBF 10 20 E:\ORACLE\ORADATA\GISDB\TEMP01.DBF 1 20 H:\>sdeexport -o create -l HUGE_FC,SHAPE -a all -f C:\BIG_FC_EXP -i 5151 -u user1 -p user1 ArcSDE 9.2 for Oracle10g Build 1081 Sun Sep 17 16:01:22 2006 SDEX File Export Administration Utility ----------------------------------------------------- Exporting ArcSDE object to "C:\BIG_FC_EXP" in SDEX export format ... Exporting table "HUGE_FC". Spatial column "SHAPE" 14793224 features converted. 14793224 features exported.

Like every Disney movie, this one had a happy ending too.