1 2 Previous Next 17 Replies Latest reply: Aug 8, 2006 4:03 AM by 526497 RSS

    ORA-29855: error occured in ODCIINDEXCREATE routine

    433479
      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?
      Thanks!
        • 1. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
          415914
          Rebecca

          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.

          Udo Martin
          • 2. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
            433479
            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
            • 3. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
              33450
              Rebecca,

              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)?

              Bryan
              • 4. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                433479
                Bryan,

                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.

                Thanks,
                Rebecca
                • 5. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                  33450
                  Rebecca,

                  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:

                  http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:807626327693

                  Bryan
                  • 6. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                    Siva Ravada
                    Rebecca,
                    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.

                    siva
                    • 7. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                      433479
                      Siva,
                      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?
                      Thanks!
                      Rebecca
                      • 8. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                        Siva Ravada

                        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.

                        siva
                        • 9. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                          433479
                          It's 9i patch release 2 (9.2.0.6) patch set 5.
                          I don't know if I have the spatial patch.
                          • 10. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                            433479
                            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

                            Spatial

                            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.
                            • 11. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                              Siva Ravada

                              Rebecca,

                              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.

                              siva
                              • 12. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                                433479
                                ok, Siva,
                                This 'create index' problem still hasn't been solved.
                                I'm using Enterprise Edition.
                                Whatelse can I do?
                                thanks, Rebecca
                                • 13. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                                  Siva Ravada
                                  are you still get the same error as before ?

                                  siva
                                  • 14. Re: ORA-29855: error occured in ODCIINDEXCREATE routine
                                    433479
                                    siva,
                                    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!
                                    Rebecca
                                    1 2 Previous Next