How to Resolve ORA-25176: storage specification not permitted for primary key

ORA-25176

When I tried to modify an attribute of an index like the following, I got ORA-25176: storage specification not permitted for primary key.

SQL> alter index erpapp.xc176p0 logging;
alter index erpapp.xc176p0 logging
*
ERROR at line 1:
ORA-25176: storage specification not permitted for primary key

ORA-25176 means that we can't change the attribute of an index directly, because such index is actually a table, an index-organized table.

Let's check its index type.

SQL> select index_type from all_indexes where owner = 'ERPAPP' and index_name = 'XC176P0';

INDEX_TYPE
---------------------------
IOT - TOP

Solution

As you can see, it's an index-organized table (IOT). Everything you tried to perform on the index, you should do it on the original table.

Therefore, we should modify the attribute on the table instead of the index.

SQL> column stmt format a50;
SQL> select 'alter table ' || owner || '.' || table_name || ' logging;' stmt from all_indexes where owner = 'ERPAPP' and index_name = 'XC176P0';

STMT
--------------------------------------------------
alter table ERPAPP.PAY_SEG_IOT logging;

SQL> alter table ERPAPP.PAY_SEG_IOT logging;

Table altered.

The current status of the table will reflect to the index immediately.

Leave a Reply

Your email address will not be published. Required fields are marked *