Re: Do selects block truncates?

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 20 Dec 2007 10:15:13 -0800
Message-ID: <bf46380712201015l14c7e2f1saf9e2c587f62f967@mail.gmail.com>

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

Received on Thu Dec 20 2007 - 12:15:13 CST