This content has been marked as final. Show 17 replies
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
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.
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!