The geodatabase system table could not be created. [GDB_Items : Error (-51)]

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:

Image

 

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;

Image

 

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;

Image

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;

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

while upgrading ArcSDE Geodatabase from version 9.3.1 SP2 to version 10 SP5, the below error was faced:

Underlying DBMS error [Layer not found] [Error executing PL/SQL Block db_stgeom_create_indexORA-29833: indextype does not exist]

This error indicates that you are missing an indextype in your ArcSDE Geodatabase in Oracle, the indextype name is: “ST_SPATIAL_INDEX”

when attempting to execute the following Sql Query to create the index type:

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

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 following error was received:

ORA-29830: operator does not exist
ORA-29830: operator does not exist

This error indicates that for some reason there are missing operators in your ArcSDE geodatabase, you need to create them.

but, first you need to query the complete list of operators from another ArcSDE geodatabase that is intact and healthy, using the query: select * from user_operators;

Image

compare the list of operators where the ArcSDE geodatabase that has the upgrade issue, and create the missing ones.

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;

ST_SPATIAL_INDEX indextype
ST_SPATIAL_INDEX indextype

Re-Run the Upgrade, and it will be successful.

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;

If this is not the case you are facing….but facing the same error then try the following:

 

SQL> GRANT EXECUTE ON sde.st_spatial_index TO SCHEMA_NAME;

OR

SQL> GRANT EXECUTE ON sde.st_spatial_index TO PUBLIC;

AND

SQL> GRANT CREATE INDEXTYPE TO SCHEMA_NAME;