INTRODUCTION
I was recently confronted with an error ORA-01502 error. This occurred as I was attempting to perform a DML operation on a table. Initially, I was surprised but after some digging I managed to get to the bottom of the issue. Here’s what happened.
SETUP
Firstly, we create a sample table, create a unique index and primary key on that table and then Insert 100 records into the table.
DROP TABLE IF EXISTS test_hf ;
Table IF dropped.
CREATE TABLE test_hf (
hf_key NUMBER
,hf_val VARCHAR2(128 CHAR)
)
/
Table created.
CREATE UNIQUE INDEX test_hf_pk
ON test_hf (hf_key)
/
Index created.
ALTER TABLE test_hf
ADD CONSTRAINT test_hf_pk PRIMARY KEY (hf_key)
/
Table altered.
INSERT INTO test_hf
SELECT rownum, dbms_random.string('x',12)
FROM dual
CONNECT BY LEVEL <= 100
/
100 rows created.
COMMIT ;
Commit complete.Now that we have a sample table we can recreate the issue.
The problem
The issue is due to an ALTER TABLE MOVE which was performed on the table. This was probably the wrong operation to use in this scenario to begin with as my aim was to reclaim space.
According to this article from asktom; ALTER TABLE SHRINK SPACE is used to reclaim space whereas space reclamation is merely a side effect of ALTER TABLE MOVEALTER TABLE MOVE causes the associated index(es) to become unusable, hence the ORA-01502 error message.
Alter table move versus alter table move online
In oracle Database 12.1 alter table move was introduced with the ability to move table partitions and sub-partitions online. However, In Oracle Database 12c Release 2 (12.2) you can now perform an online move of a table, as well as individual partitions and sub-partitions. With the former, indexes are unusable after the operation and need to be rebuilt whereas with the latter, indexes remain usable and valid.
Here are some differences, tabularized :
| FEATURE | ALTER TABLE MOVE(12.1 onward) | ALTER TABLE MOVE ONLINE(12.2 onward) |
| Concurrency | No INSERT, UPDATE, or DELETE operations are allowed during the move. | Users and applications can continue to query and modify the table during the move |
| Indexes | Marks all indexes UNUSABLE, requiring a subsequent rebuild. | Indexes remain VALID and usable throughout and after the operation. |
Now that we understand the problem, Let’s reproduce it.
Demonstration
We query the index status, perform an ALTER TABLE MOVE of the table and query the index status again.
SELECT index_name, status
from user_indexes
where table_name = 'TEST_HF';
INDEX_NAME STATUS
_____________ ___________
TEST_HF_PK VALID
SQL> ALTER TABLE test_hf MOVE ;
Table altered.
SELECT index_name, status
from user_indexes
where table_name = 'TEST_HF';
INDEX_NAME STATUS
_____________ ___________
TEST_HF_PK UNUSABLENext, we then attempt to perform a DML operation on the table
SQL> DELETE FROM test_hf ;
Error starting at line : 1 in command -
DELETE FROM test_hf
Error report -
ORA-01502: index 'HF.TEST_HF_PK' or partition of such index is in unusable state
SQL>We get the ORA-01502 error .
Solution
In order to fix this an index rebuild is required
SQL> ALTER INDEX test_hf_pk REBUILD ;
Index TEST_HF_PK altered.We verify the index status
SQL> SELECT index_name, status
2 from user_indexes
3* where table_name = 'TEST_HF';
INDEX_NAME STATUS
_____________ _________
TEST_HF_PK VALID
SQL>
DML can now be performed again
SQL> DELETE FROM test_hf;
100 rows deleted.
SQL>
SQL> INSERT INTO TEST_HF
2* VALUES (1, 'TEST ROW');
1 row inserted.
SQL>
SQL> COMMIT ;
Commit complete.
SQL>ALTER TABLE MOVE ONLINE
If we attempt the same operation again using ALTER TABLE MOVE ONLINE the index remains valid
SQL> SELECT index_name, status
2 from user_indexes
3* where table_name = 'TEST_HF';
INDEX_NAME STATUS
_____________ _________
TEST_HF_PK VALID
SQL>
SQL>
SQL> ALTER TABLE TEST_HF MOVE ONLINE ;
Table TEST_HF altered.
SQL>
SQL>
SQL>
SQL> SELECT index_name, status
2 from user_indexes
3* where table_name = 'TEST_HF';
INDEX_NAME STATUS
_____________ _________
TEST_HF_PK VALID
SQL>
SQL>
SQL>
SQL> INSERT INTO TEST_HF
2* VALUES (2, 'TWO');
1 row inserted.
SQL>
SQL> COMMIT ;
Commit complete.
SQL>As seen above, the ALTER TABLE MOVE ONLINE does not require an online rebuild of the index prior to performing any DML and we get no ORA-01502 error. there are other limitations to using this operation which can be found in the oracle docs.
CONCLUSION
The database docs for this error state the cause as “An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation”. Dropping or Rebuilding the index fixes the issue. The primary purpose of move is changing a table or partition’s storage attributes and as a side effect space can be reclaimed. An ALTER TABLE MOVE ONLINE
Cheers, Harris.