Mark, 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 224-405-4079 "Bobak, Mark" <Mark.Bobak@proqu est.com> To <Chris.Taylor@xxxxxxxxxxxxxxx>, 12/20/2007 01:01 <genegurevich@xxxxxxxxxxxx>, PM "oracle-l" <oracle-l@xxxxxxxxxxxxx> cc Subject RE: Do selects block truncates? Gene, 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 -- Mark J. Bobak Senior Database Administrator, System & Product Technologies ProQuest 789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346 +1.734.997.4059 or +1.800.521.0600 x 4059 mark.bobak@xxxxxxxxxxxxxxx www.proquest.com www.csa.com ProQuest...Start here. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Taylor, Chris David Sent: Thursday, December 20, 2007 1:08 PM To: genegurevich@xxxxxxxxxxxx; 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: chris.taylor@xxxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of genegurevich@xxxxxxxxxxxx Sent: Thursday, December 20, 2007 11:44 AM To: oracle-l Subject: Do selects block truncates? Hi all: 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? thank you Gene Gurevich -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l