Stupid analyze question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Stupid analyze question

  1. #1
    Join Date
    Jan 2001
    Posts
    3,131

    Stupid analyze question

    Is a table locked when you do an analyze?

    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    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.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    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
    Last edited by Mr.Hanky; 02-03-2003 at 11:37 AM.
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width