Onlinerebuildindex exception termination encountering ORA-08104

Createindexidx_p_merchant_detail_idonD_ORDER_DETAIL (merchant_detail_id) Online

Create index idx_p_merchant_detail_id on D_ORDER_DETAIL (merchant_detail_id) Online

Create index idx_p_merchant_detail_id on D_ORDER_DETAIL (merchant_detail_id) Online;

After the index is created for a long time, it is not reflected. Then, when the index is deleted, the following error is returned:

Error: ORA-08104: this index object 67420 is being online built or rebuilt

Create an index using the ONLINE parameter (or re-create an index). If the process is suddenly terminated, or you manually CTRL + C to cancel this operation,
In a few cases, it is troublesome. Re-create an index will tell you that the index already exists. drop index will tell you the index
The cited lock or the ORA-08104 (this index object xxxxx is being online built or rebuilt) error.

SMON is responsible for clearing some temporary objects created before this process fails. Unfortunately, SMON is not so obedient and will be cleared immediately,
This purge may take a long time. It is said that the purge will be cleared in 2 hours on 9i.

How can this problem be solved?
Before Oracle10g, there was no good way to deal with this situation. It was only necessary to wait for the SMON process to clean up. It is said on the Internet that restarting the database can solve the problem,
It is said that directly update the system table ind $ is an undesirable solution for product libraries that cannot be stopped. Restarting is unrealistic. modify the system table.
It is also a secret of DBAs.

It is best to wait for the system to automatically clear it. If the impact on you causes you to manually clear it as soon as possible, it depends on your luck.
If it is Oracle10g, you can use dbms_repair.online_index_clean to manually clean it (metalink said that 9i can also use this process if it has patched Bug 3805539 ).

If you are using a busy OLTP system and want to maintain a single column index, the risk is very high. Clear these links on SMON
Before the object, there is no way to create a new index on the column. Can the server support it?

When an exception is terminated, you can find that the status of the ind $ index is still online rebuild:

SQL & gt; select obj #, flags from ind $ where obj # = 67420;
OBJ # FLAGS
--------------------
67420 514

Description of the Flags field can be found in the SQL. bsq script of ind $:

/* Mutable flags: anything permanent shocould go into property */
/* Unusable (dls): 0x01 */
/* Analyzed: 0x02 */
/* No logging: 0x04 */
/* Index is currently being built: 0x08 */
/* Index creation was incomplete: 0x10 */
/* Key compression enabled: 0x20 */
/* User-specified stats: 0x40 */
/* Secondary index on IOT: 0x80 */
/* Index is being online built: 0x100 */
/* Index is being online rebuilt: 0x200 */
/* Index is disabled: 0x400 */
/* Global stats: 0x800 */
/* Fake index (internal): 0x1000 */
/* Index on UROWID column (s): 0x2000 */
/* Index with large key: 0x4000 */
/* Move partitioned rows in base table: 0x8000 */
/* Index usage monitoring enabled: 0x10000 */

514 = 0 × 202, indicating that the index status is index is being online rebuilt: 0 × 200 + analyzed: 0 × 02

After the SMON cleanup is completed, the index status has been restored to normal again:

SQL & gt; select obj #, flags from ind $ where obj # = 67420;
OBJ # FLAGS
--------------------
67420 2

After cleaning, you can see the following records in alert. log:

User:, time: 20071209 03:12:09, program: oracle @ db1
(SMON), IP:, object: SYS_JOURNAL_67420, DDL: drop table "TAOBAO". "SYS_JOURNAL_67420"