RE: Do selects block truncates?
- From: "Tony Aponte" <Tony_Aponte@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Dec 2007 13:45:54 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
Sent: Thursday, December 20, 2007 1:15 PM
To: genegurevich@xxxxxxxxxxxx
Cc: oracle-l
Subject: Re: Do selects block truncates?
On Dec 20, 2007 9:43 AM, <genegurevich@xxxxxxxxxxxx> 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
Other related posts:
