Example Analysis of DML Operation failure caused by ORACLE Partition Index UNUSABLE
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!
