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

This article mainly explains "ORACLE partition index UNUSABLE caused by DML operation failure example analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn the "ORACLE partition index UNUSABLE caused by the DML operation failure example analysis"!

1. Create a simulated partition table

SQL > CREATE TABLE T_NEW (ID int, TIME date,name varchar2 (20)) PARTITION BY RANGE (TIME)

(PARTITION P1 VALUES LESS THAN (TO_DATE ('2018-1-1,' YYYY-MM-DD'))

PARTITION P2 VALUES LESS THAN (TO_DATE ('2018-2-1,' YYYY-MM-DD'))

PARTITION P3 VALUES LESS THAN (TO_DATE ('2018-3-1,' YYYY-MM-DD'))

PARTITION P4 VALUES LESS THAN (MAXVALUE); 2 3 4 5

Table created.

SQL >

2. Insert data test partition table is available

SQL > insert into t_new values ('2018-1-15 days,' YYYY-MM-DD'), 'hellow')

1 row created.

SQL >

SQL > commit

Commit complete.

SQL > select * from t_new

ID TIME NAME

1 15-JAN-18 hellow

SQL >

3. Create an invisible and unavailable partition index for business changes

SQL > create unique index IDX1 on t_new (id, time) local invisible nologging unusable

Index created.

SQL >

4. Failed to execute INSERT insert. Prompt: ORA-01502 partition index status is unavailable

SQL > insert into t_new values ('2018-3-15 days,' YYYY-MM-DD'), 'yellow')

Insert into t_new values ('2018-3-15 days,' YYYY-MM-DD'), 'yellow')

*

ERROR at line 1:

ORA-01502: index 'TEST.IDX1' or partition of such index is in unusable state

SQL >

5. Recreate the index and remove the UNUSABLE keyword

SQL > drop index idx1

Index dropped.

SQL > create unique index IDX1 on t_new (id, time) local invisible nologging

Index created.

SQL >

6. Analog INSERT insertion is not affected.

SQL > insert into t_new values ('2018-3-15 days,' YYYY-MM-DD'), 'yellow')

1 row created.

SQL >

At this point, I believe that everyone on the "ORACLE partition index UNUSABLE caused by DML operation failure example analysis" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.