Adding unique constraint with enable validate locks table?
TABLE: 128 Partition by Hash (LNAME)
ROWS: 300 Million rows
DB: Oracle 10.2.0.4
RAC DB: 5 nodes on Linux
Question:
when I run the following statement to add constraint with enable validate,wHY does this add constraint statment block the simple select on this PROD.T_PERSON table,. Is there a Oracle doc that explain this behavior.
select * from PROD.T_PERSON;
*****************************************************
BUILDING CONSTRAINT with ENABLE VALIDATE:
alter table PROD.T_PERSON add CONSTRAINT U_PERSON2
UNIQUE (CID, SYSTEMCODE) DEFERRABLE INITIALLY DEFERRED
USING INDEX PROD.U_PERSON2 ENABLE VALIDATE;
UNIQUE KEY INDEX:
CREATE INDEX PROD.U_PERSON2 ON PROD.T_PERSON
(CID, SYSTEMCODE)
NOLOGGING
DB: Oracle 10.2.0.4
RAC DB: 5 nodes on Linux
Question:
when I run the following statement to add constraint with enable validate,wHY does this add constraint statment block the simple select on this PROD.T_PERSON table,. Is there a Oracle doc that explain this behavior.
select * from PROD.T_PERSON;
*****************************************************
BUILDING CONSTRAINT with ENABLE VALIDATE:
alter table PROD.T_PERSON add CONSTRAINT U_PERSON2
UNIQUE (CID, SYSTEMCODE) DEFERRABLE INITIALLY DEFERRED
USING INDEX PROD.U_PERSON2 ENABLE VALIDATE;
UNIQUE KEY INDEX:
CREATE INDEX PROD.U_PERSON2 ON PROD.T_PERSON
(CID, SYSTEMCODE)
NOLOGGING
0