6 Replies Latest reply: Jul 8, 2008 10:43 PM by 584650 RSS

    How to get rid of 2 objects after failed online re-indexing

    648880
      Hi,

      I wonder if anyone has encountered this or knows what to do with two remaining objects that originates from a failed index rebuild.

      There was an attempt to rebuild indexes online and for some reason with following error:
      ########################################
      ALTER INDEX ODS.RP_DN
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-00933: SQL command not properly ended
      ########################################
      After the attempt there are the following objects left behind:
      - index SYS_IOT_TOP_5285
      - table SYS_JOURNAL_4927

      I'm not sure whether it is safe to just drop these two objects or not? I tried to rename both of them, but was not able to rename the index:
      ############################################
      alter index ODS.SYS_IOT_TOP_5285 rename to OLD_SYS_IOT_TOP_5285
      *
      ERROR at line 1:
      ORA-25176: storage specification not permitted for primary key
      ##############################################

      So if anyone has any knowledge on this, it would be much appreciated.
        • 1. Re: How to get rid of 2 objects after failed online re-indexing
          513339
          hi

          kindly see this link

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/data_acc.htm#i17778

          hope this helps

          CHeers
          • 2. Re: How to get rid of 2 objects after failed online re-indexing
            Randolf Geist
            Your index rebuild attempt obviously hit a bug. There is a similar bug for older versions of Oracle when attempting to (re-)build an unique index online and the recursive sql generated for checking the uniqueness is malformed.

            Which version of Oracle is this database?

            Manually manipulating the remaining objects is dangerous and can lead to ORA-600 errors when accessing the base object. If you run into this ORA-600 errors you need to bounce the database instance, that should fix the issue.

            The remaining objects are supposed to be cleaned up by the SMON background process but it can take him a long time in busy systems(up to two days says Oracle). As long as there are ongoing transactions on the object the clean up procedure might not be able to complete and will attempt to re-try depending on Oracle version.

            In more recent releases of Oracle (at least 10.2.0.1 and later) there should be a DBMS_REPAIR package that offers a function ONLINE_INDEX_CLEAN that manually invokes the cleanup procedure that SMON should be doing anyway, but it still may not be able to do the clean up due to concurrent DML activities.

            If DBMS_REPAIR is not available in your database you can check for the installation script in the following location, but in older releases it does not contain the ONLINE_INDEX_CLEAN function. I'm not sure if this is available as backport to previous releases resp. as one-off patch:

            {ORACLE_HOME}/rdbms/admin/dbmsrpr.sql

            Regards,
            Randolf

            Oracle related stuff:
            http://oracle-randolf.blogspot.com/

            SQLTools++ for Oracle:
            http://www.sqltools-plusplus.org:7676/
            http://sourceforge.net/projects/sqlt-pp/
            • 3. Re: How to get rid of 2 objects after failed online re-indexing
              648880
              This DB is 9i. These objects have been there for more than a month, so I think they're not going to be cleaned at all without manual intervention...
              • 4. Re: How to get rid of 2 objects after failed online re-indexing
                648880
                To be more precise the version is:

                Oracle9i Enterprise Edition     Release 9.0.1.4.0 - Production
                PL/SQL                    Release 9.0.1.4.0 - Production
                CORE                    9.0.1.2.0 Production
                TNS for Solaris:          Version 9.0.1.4.0 - Production
                NLSRTL                    Version 9.0.1.4.0 - Production

                Anyway of cleaning up in this revision?

                Thanks for your help
                • 5. Re: How to get rid of 2 objects after failed online re-indexing
                  Hans Forbrich
                  There was an attempt to rebuild indexes online and
                  for some reason with following error:
                  ########################################
                  ALTER INDEX ODS.RP_DN
                  If I'm not mistaken, the standard ODS schema is for the Oracle Directory Server (LDAP).

                  Why do I keep thinking that this is an object-relational domain index? If it is, you would get relevant information from DBA_INDEXES columns
                  PARAMETERS
                  DOMIDX_STATUS
                  DOMIDX_OPSTATUS

                  and you
                  1) would need to rebuild it using the 'indextype' clause;and
                  2) could drop it by appending the 'FORCE' option to the DROP INDEX statement.
                  • 6. Re: How to get rid of 2 objects after failed online re-indexing
                    584650
                    Yet another reason why you should never rebuild indexes online (receiving ora-0600) regardless of what it's supposed to do.