indexes are in N/A status

Hi All,
I have created an index on a partition table. But the index is in N/A status, Index is composite index (on 6 columns). All columns are there in table.

How can i make it in valid status ?

Thanks in advance

look in user_ind_partitions, not user_indexes

This is normal. You have created local indexes for a partitioned table.

Code:

---

SQL> Create table emp (
  2  Emp number,
  3  Name varchar2(20))
  4  Partition by range (emp)
  5  (Partition emp1 values less than (100),
  6  Partition emp2 values less than (200),
  7  Partition emp3 values less than (300));

Table created.

SQL> select index_name from user_indexes where table_name='EMP';

no rows selected

SQL> CREATE INDEX ind_emp ON emp(emp) LOCAL;

Index created.

SQL> select index_name ,status from user_indexes where table_name='EMP';

INDEX_NAME                    STATUS
------------------------------ --------
IND_EMP                        N/A
SQL> select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions;

INDEX_NAME                    PARTITION_NAME                STATUS
------------------------------ ------------------------------ --------
IND_EMP                        EMP2                          USABLE
IND_EMP                        EMP3                          USABLE
IND_EMP                        EMP1                          USABLE

---

See the USER_IND_PARTITIONS for the actual STATUS.