Re: Do selects block truncates?

Jared,

I see your point and I can't reconcile it with what I am seeing.

thank you

Gene Gurevich
Oracle MySQL Operations - OMO
224-405-4079

                                                                           
             "Jared Still"                                                 
             <jkstill_at_gmail.co                                             
             m>                                                         To 
                                       genegurevich_at_discover.com           
             12/20/2007 12:15                                           cc 
             PM                        oracle-l <oracle-l_at_freelists.org>   
                                                                   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 Received on Thu Dec 20 2007 - 13:29:30 CST