I'm trying to create a spatial index using Microstation Geographics, but I got error messages.
The first error msg was ORA-29855: error occurred in ODCIINDEXCREATE routine. I think the other messages were also related to that routine.
I tried to create a spatial index (using the same method) in another database. It worked.
I wonder what went wrong. Do I need records in the table to create index?
It is not necessary to have records in a table. You can create the index on a empty table.
Be sure to insert the metadata for your table in the user_sdo_geom_metadata Table for your table.
Please post the other Error Rows, because the include additional hints.
Thanks Udo. I have the records in the USER_SDO_GEOM_METADATA table. I actually used the interface in Microstation Geographics to insert metadata and create index. But I don't think that matters, because I tried to create the index in SQL*Plus and got the same error messages. Here're the syntax and errors:
SQL> CREATE INDEX CSRLP.CUTSLP_FIXED_X
2 ON CSRLP.CUTSLP_GEO (CUTSLP_GEOM)
3 INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS (' SDO_LEVEL=6
4 LAYER_GTYPE=COLLECTION TABLESPACE=USERS ');
CREATE INDEX CSRLP.CUTSLP_FIXED_X
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-06550: line 1, column 8:
PLS-00201: identifier 'MDSYS.MDPRVT_IDX' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 7
ORA-06512: at line 1
1. Is the tablespace the same for the table and the spatial index (USERS)? If not (my guess), leave out the TABLESPACE=USERS and try it again.
2. Are you sure you want to use a quad-tree index (I assume this is on 8 or 9i)?
Yes, the table and the spatial index are in USERS. If I leave out the TABLESPACE=USERS, it will put it in the same tablespace with the table?
I use quad-tree because the database will be updated frequently. Am I right to choose quad-tree? Yes, 9i.
Yes, it will go in the same tablespace (assuming you create the table and index as the same user with no schema prefixes).
As for the quad-tree issue, I'd search on quad in this forum and let you be the judge. I'd personally not use it since:
A. It's not supported after 9ir2
B. Pain to maintain
C. Slower Overall
Yes, spatial data inserts with indexes are slower than most other Oracle indexes. However, unless you are doing a ton of them (ETL?) it's not that bad. If your app isn't 24/7, then I'd drop the index, do the inserts, and then re-create the index (much faster). Or if you have to be 24/7, you could mark the index unusable while doing the inserts. See:
It looks like there were some patches applied to the
DB recently, but the spatial part of the patches
are not applied correctly.
Can you check if there are any recent patches
to the system ?
The create index is complaining about a missing package, so
it is likely due to a recent patch.
Yes, that makes sense! I recently install the 9i patch, but not sure if I installed it properly or I missed some steps after I installed it. Should I uninstall and reinstall it? What should I do after install?
NO need to un-install the patch, but you need to install
the Spatial patch also.
There should be something about spatial patch in the README
for the patch.
Can you post the exact patch number that you installed.
In the patch readme, I found this:
If you are using an Oracle9i (9.2) preconfigured Standard Edition database, then the database contains the following components that are not supported by the Standard Edition:
Oracle Data Mining
Oracle OLAP Catalog
Oracle OLAP Analytic Workspace
Oracle OLAP API
The catpatch.sql script does not run the patch scripts for these components. It sets the component STATUS to "OPTION OFF" in the DBA_REGISTRY view. The original versions of the dictionary objects for the components remain in the database but the catpatch.sql script does not apply the patch to them.
Does it mean it doesn't support Spatial? I'm not sure mine is preconfigured Standard Edition database though.
If you have SE database, you can manually install the spatial part of the patch.
Connect to the DB as "system as sysdba"
and run $OH/md/admin/sdopatch.sql file.
Assuming you got new patched files for Spatial, this
should fix the index problems.
You can check the time stamps of the files in that md/admin/
directory to see if you have any new files in that directory
that are shipped as part of the patch.
yes, got the same error. I reinstalled oracle without patch, and it's working now!
but now I have another problem. I don't know if I did something wrong with the listener or what, I can't discover the database in the Console. The node and the listener are discovered, but not the database. It doesn't even let me configure manually. What can I do? Can I just uninstall the listener and reinstall it again? (I deleted and created it many times, and still get the same problem)
Thanks a lot!