When i wanted to upgrade my ArcSDE Geodatabase from version 9.3.1 SP2 to 10.1 SP1. I ran the pre-requisite tool which scanns fully my geodatabase and stated the scan passed successfully and your geodatabase is ready for upgrade.

After running the actual upgrade, i faced the below error:

//———————————————————————————————————————–

]Running Pre-Requisite check (C:\Users\XXX\AppData\Roaming\ESRI\Desktop10.1\ArcCatalog\XXX.sde).
Instance supports XML type.
Server tables and stored procedures are up to date.
The geodatabase system table could not be created. [GDB_Items : Error (-51)]
The geodatabase system table could not be created. [GDB_Items : Error (-51)]Unpaused the database.
Failed to execute (UpgradeGDB).
Failed at Thu Dec 26 09:38:54 2013 (Elapsed Time: 13.00 seconds)

//——————————————————————————————————————-

so, at the begining i though it could be something related to “missing privileges”  , which was NOT !!!

after more digging, i checked my local ArcGIS Desktop Logs (located under your user profile: C:\Users\youruserprofile\AppData\Local\ESRI\Desktop10.1)

from sde_setup.log file:

***********************************************************************************************************************

[Sun Jan 05 07:58:31 2014] SQL Stmt: <alter indextype SDE.st_spatial_index using SDE.st_domain_methods>
[Sun Jan 05 07:58:44 2014] St_Geometry type created or updated…

[Sun Jan 05 07:58:48 2014] Error creating GDB_Items table…
[Sun Jan 05 07:58:48 2014] ERROR Creating Geodatabase tables
, Error = -511
,EXT_Error = 29855
,EXT_ERROR1 = ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at “CTXSYS.DRUE”, line 160
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366

************************************************************************************************************************

The errors seems to me to reference two issues (st_spatial_index issue) and (CTXSYS which is the Oracle Text).

The first thing i have performed is to re-validate my Oracle Text Component, which was performed successfully.

from sdedc_Oracle.log file:

***********************************************************************************************

PLS-00201: identifier ‘SDE.GDB_UTIL’ must be declared
ORA-06550: line 1, column 26:
PL/SQL: Statement ignored

*********************************************************************************************************************

after examining the GDB_items table in another ArcSDE Geodatabase version 10, i found out that the table has a “domain index” associated with it being created by an indextype named st_spatial_index .

sooo BOOM i found the real issue which indextype is for some reason missing from my Geodatabase !!!

when i tried to create the index type using this query:

=========================================================================

CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX” FOR ”SDE”.”ST_BUFFER_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, NUMBER), ”SDE”.”ST_CROSSES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2),”SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2),  “SDE”.”ST_ENVINTERSECTS” (“SDE”.”ST_GEOMETRY”, NUMBER, NUMBER, NUMBER, NUMBER), ”SDE”.”ST_EQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_INTERSECTS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_ORDERINGEQUALS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),  “SDE”.”ST_OVERLAPS” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”), ”SDE”.”ST_RELATE” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”, VARCHAR2), ”SDE”.”ST_TOUCHES” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”),”SDE”.”ST_WITHIN” (“SDE”.”ST_GEOMETRY”, “SDE”.”ST_GEOMETRY”)USING “SDE”.”ST_DOMAIN_METHODS” WITH LOCAL RANGE PARTITION;

*********************************************************************************************************************

the error faced is the following:

 

so the inextype could not be created because there are missing “operators” for some reason ??!!!

so i compared the list of operators between my current geodatabase that i need to upgrade to another 10.0 geodatabase instance, using the sql query: select * from user_operators;

 

after comparing the two lists, i found out the “missing” operators and i have created them.

In order to extract the sql definition of the operator use the following query as an example:

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;

after creating all missing operators, try executing the previous sql query mentioned: “CREATE OR REPLACE INDEXTYPE “SDE”.”ST_SPATIAL_INDEX“

then use the following sql, to check indextype was created:

select * from user_indextypes;

I re-ran the upgrade again, and it was SUCCESSFUL !

SQL Query Reference:

Query Reference:

select * from user_operators;

select * from user_indextypes;

select dbms_metadata.get_ddl(‘INDEXTYPE’,’ST_SPATIAL_INDEX’) from dual;

select dbms_metadata.get_ddl(‘OPERATOR’,’ST_WITHIN’) from dual;