RE: Do selects block truncates?

>"A SELECT needs to hold a TM enqueue when it is
running so that the data structure does not change underneath it when it is running due to DDL (including TRUNCATE which is considered DDL)"

No way. SELECT(with no for update clause) never acquires TM lock. No no. I think this is kind of misunderstanding.

SELECT statement follows following steps 1. parse : acquires library cache lock(aka DDL lock) and downgrade it to null mode(aka breakable parse lock)

2. execute : acquires library cache pin and release it
3. fetch: breakable parse lock
4. close: release breakable parse lock

SELECT really blocks truncate(DDL) at parse step. It's reasonable, isn't it? Parse step involves identifying dictionary information so that dictionary shouldn't be altered by DDL.
For this reason, library cache lock is often called as "DDL lock".

But at fetch step? No way. We can prove it with really simple test case. And DDL lock is library cache lock. It's not TM lock.

I don't still get the concept of read consistency mechanism blocking DDL. Can anyone show me simple replayable test case? Or official documents from Oracle or authorized organizations?  

Without proof, I wouldn't believe it. :(

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Saturday, December 22, 2007 12:50 AM To: oracle-l
Subject: RE: Do selects block truncates?

All:

I have received an Email from someone on this list (it went directly to me) which seems to provide another explanation:

"A SELECT needs to hold a TM enqueue when it is running so that the data structure does not change underneath it when it is running due to DDL (including TRUNCATE which is considered DDL)"

thank you very much to all who replied

Gene Gurevich

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l