I doubt the issue is bad geometry - if that was the case then the index wouldn't have built on your source database. Are you getting any errors logged during the import? Check the log carefully, the error might be quite subtle. If you're still unsure, then you could try tracing the impdp process - have a look here for details of how to trace an impdp: http://arjudba.blogspot.ie/2009/04/how-to-tracediagnosis-oracle-data-pump.html
can you provide the impdp log error/warning messages ?
I've had a similar experience, which happened because the Spatial metadata didn't come over (MDSYS). Check your table for indexes in the regular metadata, then compare with the spatial metadata:
select table_name, include_column, index_name, index_type from user_indexes where index_type='DOMAIN';
select * from user_sdo_index_metadata;
select * from user_sdo_index_info;
You might find that the index is registered in the regular stuff but not in the spatial stuff, in which case Spatial can't find (and thus use) the spatial index. Fixing this is easy: drop the index and recreate it.
Preventing this from happening: Take MDSYS along with the export/import.
Oh, and as an afterthought: Did the MDRT_### tables and the MDRS_### sequences get exported/imported?
Message was edited by: Stefan Jager (added afterthought)
I've scoured the log file, no errors or warning messages. I will do a trace on the impdp and try that next.
Unfortunately trace files gave me nil so a ticket has been opened. I'll update as soon as I know anything.
Still working on Oracle with this one to no avail. After experimenting I also found I can export and turn right around and import on the same machine and lose the index also. This is frustrating.
The final finding was that the issue was caused by a recent Oracle Patch. I sent in all the information concerning the bug but unfortunately I'm not sure it will get resolved quickly since Oracle was asking for more extensive testing on "our part" before even declaring it a bug. Due to the fact that the problem put us a several weeks behind on our workload, we couldn't see the feasabilty of us being required to futhur "test" a problem Oracle created and was now aware of. The bad part is that I will have to maintain our current patch level on the database in question until they resolve the issue.
BTW - What recent patch is that (so we can avoid it)?
For our export database (CIP) we have to lock it in at 17363850 patch 26. This level allowed us to successfully export with the domain index. All patch applications after that one have failed to create the domain index when usint expdp. The patch level on the system where the import is done is up to date.
I just applied the most recent patch and the same behavior exist. I know Oracle is aware of the issue but I don't they will be fixing it until they get more users who complain and/or "test" in order to provide them with more info.