Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to clear the failed index in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let's take a look at it.

When creating an index of a large table, it takes a long time to create because there is no parallel addition. At this time, if you terminate the operation, such as directly closing the terminal, and then log in to create again, you will report an error ORA-00095 that the object name has been used, and when you delete the object, you will report that the ORA-08104 index is being created.

SQL > drop index xxx.BM_IX;drop index xxx.BM_IX * ERROR at line 1:ORA-08104: this index object 1443829 is being online built or rebuiltSQL > select object_id from dba_objects where object_name='BM_IX' OBJECT_ID- 1443829$ oerr ora 0810408104, 00000, "this index object% s is being online built or rebuilt" / / * Cause: the index is being created or rebuild or waited for recovering / / from the online (re) build / / * Action: wait the online index build or recovery to complete

The explanation of ORA-08104 seen from the oerr tool is that the creation of the index did not fail, but that the creation continued after the terminal was closed. When an index is created or rebuilt, the system creates a temporary log table, which is used to store log information generated during index creation or re-index. at the same time, the FLAG field of this index in the base table IND$ will be set to BUILD or REBUILD identity, and when the index information changes, the change information will be stored in the log table. If the index creation or reconstruction fails, both the log table and the status bits in the data dictionary need to be cleaned by the background process smon.

Therefore, the index here cannot be deleted because the background process smon has not had time to clean up the corresponding temporary period and flag bits, and thinks that the online rebuild operation is still in progress.

So how do we stop rebuild index now? Find metalink to get a document: ORA-600 [12813] When Dropping A Table Partition After a Failed IndexRebuild (document ID 803008.1). It gives two ways:

1) use package dbms_repair package to clean up

If the database activity on the object in question can stop, simply execute the following statement:

Connect / as sysdbaselect dbms_repair.online_index_clean () from dual; exit

2) use PL/SQL block to call the dbms_repair package to clean up

If the database activity of the object in question cannot be stopped, then the following PL/SQL block handles

Note: plus dbms_repair.lock_wait indicates that it is not cleaned up immediately, and you need to keep looking for resource locks until you get it.

Declare isClean boolean;begin isClean: = FALSE; while isClean=FALSE loopisClean: = dbms_repair.online_index_clean (dbms_repair.all_index_id, dbms_repair.lock_wait); dbms_lock.sleep (2); end loop; exception when others then RAISE; end Thank you for reading this article carefully. I hope the article "how to clear the failed index in the database" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.