RE: Do selects block truncates?

Could it be that the reader is part of an XA transaction? I think I remember a paper or presentation by one of the Wise Men detailing how a reader that's part of a distributed transaction can block others simply by executing a Select.  

Tony Aponte  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Thursday, December 20, 2007 1:15 PM To: genegurevich_at_discover.com
Cc: oracle-l
Subject: Re: Do selects block truncates?  

On Dec 20, 2007 9:43 AM, <genegurevich_at_discover.com> wrote:         

        I am running oracle 10.2.0.2 and I see two sessions running truncate

	commands being stuck. It looks like they are
	waiting on another session which is currently running a long
select. That 
	select accesses both of the tables that
	the other two sessions are trying to truncate. I was not aware
that a
	select can block a truncate. Is that the case
	or is there something else here which I am missing?

I don't believe that the SELECT is blocking the truncate.

This is easy to test.

In session 1:

drop table rbg;

create table rbg
as
select *
from
dba_objects
/

insert /*+ append */
into rbg
select * from rbg;

commit;

insert /*+ append */
into rbg
select * from rbg;

commit;

insert /*+ append */
into rbg
select * from rbg;

commit;

insert /*+ append */
into rbg
select * from rbg;

commit;

In session 2:

select * from rbg;

Back to session 1:
truncate table rbg;

Back to session 2:

PUBLIC /588bb8e2_ObjectStreamField
30790
SYNONYM 08/14/2006 14:37:42 04/13/2007 17:59:12 2006-08-14:14:37:42 VALID N N N ERROR:
ORA-08103: object no longer exists

22700 rows selected.

This is on 10.2.0.3

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


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