Shapefile more than 2 GB

Error: Getting below error when attempted to load a shapefile in the database via SDE using ArcCatalog:-

Failed to convert E:\point\county_map.shp.ERROR 999999: Error executing function. A locator with this name does not exist.Failed to execute (CopyFeatures).

Environment: Oracle 10g (10.2.0.3) 32 bit, Windows 2003, ArcSDE9.2, ArcCatalog 9.2

Problem: When doing multiple attempts to load [and delete]shapefile(s) as feature class in the database, SDE doesn't clean it up when thefeature class(es) are deleted, hence some orphaned records were left behind inthe SDE repository. Hence it throws the error when attempted to load a shapefilein the same feature name. Its basically a constraint violation (CONSTRAINT_NAME='GDB_OC_UC'),but the error message that ArcCatalog throws is irrelevant and could divert fromcorrectly troubleshooting it.

Solution: Consult with DBA before doing it, you are about tomodify SDE repository table(s), it is very risky to perform it without properknowledge, it might screw up your whole database. To be on a safer side, backupthe modifying tables or whole SDE schema before attempting to do so.

Connect in SQL*Plus as SDE user and perform the following:-

SQL> conn sde/****@db1 SQL> select TABLE_NAME from user_constraints where CONSTRAINT_NAME='GDB_OC_UC'; TABLE_NAME ------------------------------ GDB_OBJECTCLASSES SQL> select NAME from sde.GDB_OBJECTCLASSES > where upper(NAME) = 'COUNTY_MAP' > and owner = 'MAP_OWNER'; NAME ------------------------------ county_map SQL> delete from sde.GDB_OBJECTCLASSES where NAME='county_map'; 1 row deleted. SQL> commit; Commit complete.

Now, the load of shapefile went smooth, like a baby's but.

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.