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: