Disabled unique index:
If the table has unique index in disabled mode, you can’t insert data into the table. It raises following error.
ORA-01502: index ‘xxxx.xxxx’ or partition of such index is in unusable state
Only solution is either to drop or rebuild this index.
eg.
SQL> drop table test_tab
Table dropped.
Elapsed: 00:00:00.14
SQL> create table test_tab as select * from user_objects where rownum < 1
Table created.
Elapsed: 00:00:01.69
SQL> select * from test_tab
no rows selected.
Elapsed: 00:00:00.02
SQL> create unique index test_tab_ui on test_tab(object_name)
Index created.
Elapsed: 00:00:00.03
SQL> alter index test_tab_ui unusable
Index altered.
Elapsed: 00:00:00.01
SQL> select index_name, status from user_indexes where index_name like ‘TEST_TAB%’
INDEX_NAME STATUS
—————————— ——–
TEST_TAB_UI UNUSABLE
1 row selected.
Elapsed: 00:00:00.15
SQL> insert into test_tab
select * from user_objects where rownum < 11
insert into test_tab
select * from user_objects where rownum < 11
Error at line 18
ORA-01502: index ‘<schema>.TEST_TAB_UI’ or partition of such index is in unusable state
Local Unique index:
It is not possible to create a local unique index on table column that is NOT used in partition . When you try to create local unique index, it raises following error;
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
eg:
SQL> drop table test_tab
Table dropped.
Elapsed: 00:00:00.15
– Creating table as user_objects
— notice that it is partitioned by column “object_id”
— Do not matter what kind of partition it is .
SQL> —
SQL> CREATE TABLE TEST_TAB
(
OBJECT_NAME VARCHAR2(128 BYTE),
SUBOBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19 BYTE),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19 BYTE),
STATUS VARCHAR2(7 BYTE),
TEMPORARY VARCHAR2(1 BYTE),
GENERATED VARCHAR2(1 BYTE),
SECONDARY VARCHAR2(1 BYTE),
NAMESPACE NUMBER,
EDITION_NAME VARCHAR2(30 BYTE)
)
partition by hash(object_id) PARTITIONS 32
Table created.
Elapsed: 00:00:00.09
—
— Creating index on “data_object_id” column which is not part of the partition.
SQL> CREATE UNIQUE INDEX TEST_TAB_UI ON TEST_TAB
(DATA_OBJECT_ID)
LOCAL
CREATE UNIQUE INDEX TEST_TAB_UI ON TEST_TAB
(DATA_OBJECT_ID)
LOCAL
Error at line 30
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
However, this index can be created as non-unique.
SQL> CREATE INDEX TEST_TAB_UI ON TEST_TAB
(DATA_OBJECT_ID)
LOCAL
Index created.
Elapsed: 00:00:01.07
Tested DB : 11g
Oracle Enablement 