4 Replies Latest reply: Feb 24, 2014 9:11 PM by onkar.nath RSS

    index problem

    1830597

      Hi,

       

      I am trying to create index schemauser.index name_A_IDX2 on schemaname.table

       

      but it gives me following error

       

      ORA-00955: name is already used by an existing object

       

      but when i try to drop the index by

       

      drop index schemaname.indexname it says

       

      ORA-01418: specified index does not exist

       

      WHY!!

       

      please help!!

        • 1. Re: index problem
          Barbara Boehmer

          It could be that your existing schemauser.index name_A_IDX2 is not an index.  You could query dba_objects to find out what it is.  Or, it could be that you need to purge your recycle bin to remove some remnant.

          • 2. Re: index problem
            Emad Al-Mousa

            what is the output result of this query:


            SQL> select owner ,index_name, status from dba_indexes where status ='UNUSABLE';


            is your index listed ?


            if YES...then

            SQL> alter index schemauser.index name_A_IDX2 rebuild online;


            you can also use "TOAD" or "Sql Developer" to check the status of this index...

             

            i hope this helps....

             

            regards,

            • 3. Re: index problem
              User2121 - -Oracle

              The error means tha t there is another object with the same name

              You can try querying from dba_objects for name with the same name

              But instead of that, I suggest you to use a different name for eg create index schemauser.<tablename>_IDX2 on schemaname.table

              • 4. Re: index problem
                onkar.nath

                Even though an index is invisible or unusable, it can be dropped. I did a small test

                 

                SQL:SCOTT@tp11g>create index idx1 on test2(sr) unusable;
                
                Index created.
                
                SQL:SCOTT@tp11g>drop index idx1;
                
                Index dropped.
                
                SQL:SCOTT@tp11g>create index idx1 on test2(sr) invisible;
                
                Index created.
                
                SQL:SCOTT@tp11g>drop index idx1;
                
                Index dropped.
                
                SQL:SCOTT@tp11g>create index idx1 on test2(sr) unusable;
                
                Index created.
                
                SQL:SCOTT@tp11g>select index_name from user_indexes where status='UNUSABLE';
                
                INDEX_NAME
                ==============================
                IDX1
                
                SQL:SCOTT@tp1
                1g>drop index idx1;
                
                Index dropped.
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                

                 

                I would suggest check the object using

                select object_name,owner,object_type from user_objects where object_name='INDEXNAME_A_IDX2';
                
                

                and drop the object and retry the operation.

                 

                Onkar