Create index online cause concurrency waits cursor: pin S wait on X on selects
Hi masters,
On 11gR2 db when we execute this statment:
ALTER TABLE GE_FRAUD_LIST ADD CONSTRAINT IDX_GE_FRAUD_LIST_ID PRIMARY KEY (ID)
USING INDEX TABLESPACE EDR_INDEX ONLINE;
and sessions that are executing queries like this..
SELECT id, fraud_level
FROM GE_FRAUD_LIST
WHERE buyer_phone = :1 AND active = 'T' AND upper(buyer_zipcode) = :2
have concurrency waits with exclusive lock!
I thought that Oracle create an IOT table and use an internal trigger to keep track of the DML changes and after apply all DML changes to the target..
Why add constraint online cause concurrency waits: cursor: pin S wait on X?