SDE 10 Domain Index does not export ORA-39127

Anonymous User · ‎08-17-2011

Original User: sherriekubis

Oracle 11.1.0.7
Linux / RAC
SDE 10 SP 1

When performing either an expdp or exp to create a full export of the database, receive this error on the domain indexes:

ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('A18008_IX1','SDECREATOR','ST_DOMAIN_METHODS','SDE',11.01.00.00.00,newblock,0)
ORA-20093: No ST_GRIDS supplied in PARAMETERS clause.
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5280

After filing an SR with Oracle their response is that ESRI's custom domain index is not supported to expdp or exp. We should drop the indexes before taking the export.

We do the export as a nightly safeguard and I see it as very risky to drop and recreate the domain indexes every night for this. Any malfunction or error could cause an outage the next day for a maintenance reason.

Has anyone else encountered this?

ForrestJones · ‎08-18-2011

Hi Sherrie,

The export of the table should work without dropping the index...Does the spatial table have a valid spatial index? Here are a couple things to check:

1- As the owner of the table (SDECREATOR) check if the table has an index:

select index_name, table_name from user_ind_columns where index_name='A18008_IX1';

INDEX_NAME                     TABLE_NAME
-------------------------- ------------------------------
A18008_IX1                         LAKES

2- Is the sde.st_geometry index information for the ST_GRIDS available?:

col owner format a12
col index_name format a10
col index_name format a15
col grid format a25

select owner, table_name, index_name,grid from sde.st_geometry_index where index_name='A18008_IX1';


OWNER        TABLE_NAME INDEX_NAME      GRID(GRID1, GRID2, GRID3)
------------ ---------- --------------- -------------------------
SDECREATOR   LAKES      A18008_IX1          SP_GRID_INFO(8, 0, 0)

Also, you can probably reproduce the problem by just exporting the single table that is failing...

As SYS:

CREATE DIRECTORY dmpdir AS '/opt/oracle';
GRANT read, write ON DIRECTORY dmpdir TO map;

AS OWNER of table:

expdp sdecreator/sdecreator DIRECTORY=dmpdir DUMPFILE=temp.dmp TABLES='LAKES'

It also might be related to the fact the instance is a RAC. I'll see if I can test that out to reproduce on a RAC server...

Thanks,

Anonymous User · ‎08-25-2011

Original User: sherriekubis

Thanks Fojo.

Here are the results:

select index_name, table_name, index_owner, table_owner from dba_ind_columns where index_name='A18008_IX1';

INDEX_NAME TABLE_NAME INDEX_OWNER TABLE_OWNER
A18008_IX1 AGSWM SDECREATOR SDECREATOR

select owner, table_name, index_name,grid from sde.st_geometry_index where index_name='A18008_IX1';

OWNER TABLE_NAME INDEX_NAME GRID
SDECREATOR AGSWM A18008_IX1 (2600, 0, 0)

The expdp fails with: I used a .par file and executed from the SYS account

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."EXPDP_SDECREATOR_AGSWM": /******** AS SYSDBA parfile=sdecreator.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 200.4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('A18008_IX1','SDECREATOR','ST_DOMAIN_METHODS','SDE',11.01.00.00.00,newblock,0)
ORA-20093: No ST_GRIDS supplied in PARAMETERS clause.
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 270
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5280
Processing object type TABLE_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
. . exported "SDECREATOR"."AGSWM" 320.7 KB 856 rows
Master table "SYS"."EXPDP_SDECREATOR_AGSWM" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXPDP_SDECREATOR_AGSWM is:
/exports/entod/exp_sdecreator_08252011_11_20.dmp
Job "SYS"."EXPDP_SDECREATOR_AGSWM" completed with 1 error(s) at 11:34:03

Anonymous User · ‎02-12-2012

Original User: nios

Hi!

Did you find a solution of the problem?

Regards
Niklas

EmadAl-Mousa · ‎04-08-2015

Hi,

i had the same problem with Oracle 11gR2 (11.2.0.3) on AIX environment.

my environment has strict security database implimentation.

you need first to grant the permission to the oracle account having these domain index problems, you may refer to: Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine ORA-01031: insufficient...

Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine ORA-01031: insufficient...

Then try to rebuild the domain indexes (it will work):

select 'alter index ' || OWNER || '.'||INDEX_NAME|| ' rebuild ;' from all_indexes where owner='ORACLE_USER' and INDEX_TYPE='DOMAIN';

lollo

Hi,

I faced very similar problem (*) during oracle expdp from Oracle 19 database but above hint didn't help me.

Even excluding that index from expdp resolve the issue. Only dropping from the database (but I can't do it in the production environment).

Does someone have any suggestion?

Thanks

Lorenzo

(*)

ORA-39127: unexpected error from call to status := SYS.DBMS_EXPORT_EXTENSION.GET_V2_DOMAIN_INDEX_TABLES('A164_IX1','SDE','ST_DOMAIN_METHODS','SDE',0,'19.15.00.00.00',1,0)
ORA-01422: exact fetch returns more than requested number of rows