Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Do selects block truncates?

RE: Do selects block truncates?

From: Ukja.dion <>
Date: Fri, 21 Dec 2007 11:47:39 +0900
Message-ID: <>

If select really blocks truncate, it's a amazing bug. :)

Anyway, why don't you post the result of v$lock and v$session_wait view for blocked truncate session? That view will show you the better picture.

I have 2 scenarios for your problem.

  1. You select session consumes so much resource so that your truncate session got slower.
  2. Truncate operation needs the flushing out to disk of corresponding buffers of target table. Because you're reading up blocks into buffer cache, your truncate session needs to flush out more blocks. (Just assumption)

 Dion Cho
 Blog:,  Wiki:

-----Original Message-----

From: [] On Behalf Of Sent: Friday, December 21, 2007 4:28 AM
To: oracle-l
Subject: RE: Do selects block truncates?


I have executed the following script (I put it at the end of the Email 'cause it is pretty lengthy).
This script shows the sessions and whether they are waiting on another session (you can
execute it on your system and see the output). When I executed it I saw two sessions running
the truncates waiting on the same session . That 'other' session was running a select
from multiple tables including the two tables that two sessions were trying to truncate. When
I killed that SELECT, the truncates went through and the two sessions completed their processing
in no time. That's what makes me thing that the SELECT blocked the two truncates.

col osuser form a12
col WAITING_4 form 99999999
col program form a30
col Command form a23
set lines 140

clear breaks
clear compute

select s.osuser, s.username, p.spid "OS PID", s.status,

       decode( command,
                0, 'No command in progress.',
                1, 'CREATE TABLE',
                2, 'INSERT',
                3, 'SELECT',
                4, 'CREATE CLUSTER',
                5, 'ALTER CLUSTER',
                6, 'UPDATE',
                7, 'DELETE',
                8, 'DROP CLUSTER',
                9, 'CREATE INDEX',
               10, 'DROP INDEX',
               11, 'ALTER INDEX',
               12, 'DROP TABLE',
               13, 'CREATE SEQUENCE',
               14, 'ALTER SEQUENCE',
               15, 'ALTER TABLE',
               16, 'DROP SEQUENCE',
               17, 'GRANT',
               18, 'REVOKE',
               19, 'CREATE SYNONYM',
               20, 'DROP SYNONYM',
               21, 'CREATE VIEW',
               22, 'DROP VIEW',
               23, 'VALIDATE INDEX',
               24, 'CREATE PROCEDURE',
               25, 'ALTER PROCEDURE',
               26, 'LOCK TABLE',
               27, 'NO OPERATION',
               28, 'RENAME',
               29, 'COMMENT',
               30, 'AUDIT',
               31, 'NOAUDIT',
               32, 'CREATE DATABASE LINK',
               33, 'DROP DATABASE LINK',
               34, 'CREATE DATABASE',
               35, 'ALTER DATABASE',
               36, 'CREATE ROLLBACK SEGMENT',
               37, 'ALTER ROLLBACK SEGMENT',
               38, 'DROP ROLLBACK SEGMENT',
               39, 'CREATE TABLESPACE',
               40, 'ALTER TABLESPACE',
               41, 'DROP TABLESPACE',
               42, 'ALTER SESSION',
               43, 'ALTER USER',
               44, 'COMMIT',
               45, 'ROLLBACK',
               46, 'SAVEPOINT',
               47, 'PL/SQL EXECUTE',
               48, 'SET TRANSACTION',
               49, 'ALTER SYSTEM SWITCH LOG',
               50, 'EXPLAIN',
               51, 'CREATE USER',
               52, 'CREATE ROLE',
               53, 'DROP USER',
               54, 'DROP ROLE',
               55, 'SET ROLE',
               56, 'CREATE SCHEMA',
               57, 'CREATE CONTROL FILE',
               58, 'ALTER TRACING',
               59, 'CREATE TRIGGER',
               60, 'ALTER TRIGGER',
               61, 'DROP TRIGGER',
               62, 'ANALYZE TABLE',
               63, 'ANALYZE INDEX',
               64, 'ANALYZE CLUSTER',
               65, 'CREATE PROFILE',
               67, 'DROP PROFILE',
               68, 'ALTER PROFILE',
               69, 'DROP PROCEDURE',
               70, 'ALTER RESOURCE COST',
               71, 'CREATE SNAPSHOT LOG',
               72, 'ALTER SNAPSHOT LOG',
               73, 'DROP SNAPSHOT LOG',
               74, 'CREATE SNAPSHOT',
               75, 'ALTER SNAPSHOT',
               76, 'DROP SNAPSHOT',
               79, 'ALTER ROLE',
               85, 'TRUNCATE TABLE',
               86, 'TRUNCATE CLUSTER',
               88, 'ALTER VIEW',
               91, 'CREATE FUNCTION',
               92, 'ALTER FUNCTION',
               93, 'DROP FUNCTION',
               94, 'CREATE PACKAGE',
               95, 'ALTER PACKAGE',
               96, 'DROP PACKAGE',
               97, 'CREATE PACKAGE BODY',
               98, 'ALTER PACKAGE BODY',
               99, 'DROP PACKAGE BODY'
       ) "Command", sid, BLOCKING_SESSION WAITING_4, s.program
  from v$session s, v$process p
 where s.username is not null

     and p.addr = s.paddr
order by 1, 5, 4

thank you

Gene Gurevich
Oracle MySQL Operations - OMO

             "Bobak, Mark"                                                 
   >                                                   To 
             12/20/2007 01:01          <>,        
             PM                        "oracle-l" <> 
                                       RE: Do selects block truncates?     


I think Chris is on the right track here. A select shouldn't take locks, therefore shouldn't block a truncate operation, unless it's a select for update. What makes you think the truncate is blocked? What does the wait interface (V$SESSION_WAIT) have to say about the truncate session? What event is it waiting on?


Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

-----Original Message-----
From: [] On Behalf Of Taylor, Chris David Sent: Thursday, December 20, 2007 1:08 PM To:; oracle-l Subject: RE: Do selects block truncates? Is he doing a 'FOR UPDATE' at the end of the SELECT? Chris Taylor Sr. Oracle DBA Ingram Barge Company Nashville, TN 37205 Office: 615-517-3355 Cell: 615-354-4799 Email:
-----Original Message-----
From: [] On Behalf Of Sent: Thursday, December 20, 2007 11:44 AM To: oracle-l Subject: Do selects block truncates? Hi all: I am running oracle 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? thank you Gene Gurevich -- -- -- --
Received on Thu Dec 20 2007 - 20:47:39 CST

Original text of this message