create spatial type in Esri Geodatabase for st_geometry

To create the spatial type (st_geoemtry) in your Oracle Geodatabase, use the Geoprocessing tool

create spatial type” which can be found in catalog tree search.

 

in order to perform this you need to configure your ‘SYS’ oracle user to connect from your client machine , this can be done through a procedure described in my early thread (https://geodatamaster.wordpress.com/2014/02/18/connecting-using-sys-user-from-client-machine/)

PLEASE NOTE: that is geoprocessing tool will ONLY work with ‘SYS’ user.

the below snapshot describes the activity:

Image

 

the dll st_geoemtry library (libst_shapelib.dll) can be found locally on your machine, in this directory:

C:\Program Files (x86)\ArcGIS\Desktop10.1\DatabaseSupport

 

 

Advertisement

the case of st_geometry in Enterprise Geodatabase design

st_geometry is one of ESRI Geodatabase storage format for Oracle database environment.

This geometry has the following highlights:

  • st_geometry is the “default” geometry storage for creating an enterprise geodatabase in Oracle DB.

 

  • If you migrated your storage from sdelob to stgeometry you’ll need to recreate your multiversion view because the structure is different.

 

  • Drop the users using the ST_GEOMETRY, before dropping the SDE user, not the other way around.Otherwise you will face ORA-21700: object does not exist or is marked for delete and as a consequence your Oracle instance is corrupted.

 

  • User defined data types such as (st_geometry) has issues with export/import database dump process in oracle.

Remark: some of these highlights are my “own” technical observation, and some are referenced by ESRI technical documentation.

Ora-21700 Object Does Not Exist Or Is Marked For Delete When Dropping a User With ST_GEOMETRY

When trying to drop the SDE user, you are getting the error:

ORA-21700: object does not exist or is marked for delete

A corruption is introduced in your ArcSDE Geodatabase instance:

1. Try to drop user SDE using drop user cascade command: DROP USER SDE CASCADE;
This will generate error if there are objects created using SDE.ST_GEOMETRY because of dependencies

2. Try to drop the tables in other schemas using the SDE.ST_GEOMETRY type
Get also error ORA-21700: object does not exist or is marked for delete

3. Back to SDE and try to drop the SDE.ST_GEOMETRY type gives error
ORA-21700: object does not exist or is marked for delete 

The Cause of this issue:

Dictionary gets corrupted when tables using a specific object type (SDE.ST_GEOMETRY) are tried to be dropped after the type was tried to get dropped 

The Solution:

the objects cannot be dropped after this type of corruption is introduced

The objects can be left there and create a new database for this cartridge to be reinstalled or create a new database and do full exp/imp

In order to avoid this problem the objects using the SDE.ST_GEOMETRY type have to be dropped before dropping the SDE schema. Also the drop user SDE cascade should not be used.

 

ESRI Reference Link: http://support.esri.com/en/knowledgebase/techarticles/detail/34483

Oracle Metal ink reference document number 9876

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;