Is a table locked when you do an analyze?
MH
Printable View
Is a table locked when you do an analyze?
MH
Analyze table estimate or compute statistics will acquire an exclusive lock on the library cache object, preventing any ddl changes, however dml on the table should be able to proceed. Analyze table validate structure, however, acquires an exclusive lock on the table, preventing any inserts/updates/deletes. In general, ANALYZE ... VALIDATE STRUCTURE requires an exclusive lock on the object being analyzed. Other permutations of ANALYZE should allow concurrent DML access.An exclusive lock doesn't prevent other people from reading the data, users should still be able to select from the table.
Issuing an analyze on an index puts a shared lock on the table. This means that you cannot do DML on the table that is locked. The DML
operation will wait for the analyze to release the lock. This lock can be viewed in v$lock. The lock Type will be TM and the Object id of the table is v$lock.id1. If there are transactions already against the table, then trying to do an analyze will give ora-54 resource busy.
Excellent, thank you.
I could not find this on tahiti, not undex "analyze" anyhow.
I am testing this in development, I have an analyze compute running and I am viewing locks under OEM and there are none visable, weird.
I am able to select on the table.
MH