| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Do selects block truncates?
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
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_at_proqu
est.com> To
<Chris.Taylor_at_ingrambarge.com>,
12/20/2007 01:01 <genegurevich_at_discover.com>,
PM "oracle-l" <oracle-l_at_freelists.org>
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_at_il.proquest.com
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Taylor, Chris David
Sent: Thursday, December 20, 2007 1:08 PM
To: genegurevich_at_discover.com; 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_at_ingrambarge.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
genegurevich_at_discover.com
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 20 2007 - 13:27:57 CST
 |
 |