NOTE: steps below are for sde 10.8.1, adjustments may be needed for other versions.
Realizing this is an old post, but for those migrating database servers without doing full export/import of db... this can be caused by importing sde schema objects in incorrect sequence, and also lack of st_spatial_index object. In Oracle I did the following to repair it:
1: run this command on your working SDE instance to get the statement to recreate the ST_SPATIALINDEX type on the broken instance:
SELECT DBMS_METADATA.GET_DDL('INDEXTYPE', 'ST_SPATIAL_INDEX', 'SDE') FROM DUAL;
Run the SQL output it gives you on the broken instance to remake the ST_SPATIAL_INDEX type.
2: In my case I also had to fix the following: regrant all sde schema grants to public, some were missing. I used the following script to export the commands to dbms_output to regrant them all:
SET SERVEROUTPUT ON SIZE UNLIMITED;
BEGIN
FOR r IN (
SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
AND grantor = 'SDE'
) LOOP
DBMS_OUTPUT.PUT_LINE('GRANT ' || r.privilege || ' ON ' || r.owner || '.' || r.table_name || ' TO PUBLIC;');
END LOOP;
END;
/
3: Then I also had to copy the gdb_items table over because it had issues importing originally:
This was best done using sql insert statements because of the CLOB and ST_GEOMETRY datatype columns involved. Use this to generate them:
select 'insert into sde.gdb_items(OBJECTID, UUID, TYPE, NAME, PHYSICALNAME, PATH, URL, PROPERTIES,
DEFAULTS, DATASETSUBTYPE1, DATASETSUBTYPE2, DATASETINFO1, DATASETINFO2,
CONTINGENTVALUES, DEFINITION, DOCUMENTATION, ITEMINFO, SHAPE) values('''
|| OBJECTID || ''',''' || UUID || ''',''' || TYPE || ''',''' || NAME || ''',''' || PHYSICALNAME || ''',''' || PATH || ''',''' || URL || ''',''' || PROPERTIES
|| ''',' || case when DEFAULTS is null then 'NULL' else ''''||to_char(DEFAULTS) || '''' end ||',''' || DATASETSUBTYPE1 || ''',''' || DATASETSUBTYPE2 || ''',''' || DATASETINFO1 || ''',''' || DATASETINFO2
|| ''',' || case when CONTINGENTVALUES is null then 'NULL' else ''''||to_char(CONTINGENTVALUES) || '''' end || ',''' || DEFINITION || ''',''' || DOCUMENTATION || ''',''' || ITEMINFO || ''','
|| case when shape is null then 'NULL);' else ' sde.st_geomfromtext(''' || to_char(sde.st_astext(shape)) || '''));' end from sde.gdb_items;
After doing those things, I compared rows counts between the old and new sde schemas and they matched again, so that seems to have resolved the issues. I can now connect again and create feature classes. You may not need to complete all of these steps as your symptoms may vary.
