RE: Do selects block truncates?
- From: genegurevich@xxxxxxxxxxxx
- To: <krish.hariharan@xxxxxxxxxxxx>
- Date: Fri, 21 Dec 2007 09:44:18 -0600
Sorry, forgot the SQL:
1 select s.osuser, s.username, p.spid "OS PID", s.status,
2 decode( command,
3 0, 'No command in progress.',
4 1, 'CREATE TABLE',
5 2, 'INSERT',
6 3, 'SELECT',
7 4, 'CREATE CLUSTER',
8 5, 'ALTER CLUSTER',
9 6, 'UPDATE',
10 7, 'DELETE',
11 8, 'DROP CLUSTER',
12 9, 'CREATE INDEX',
13 10, 'DROP INDEX',
14 11, 'ALTER INDEX',
15 12, 'DROP TABLE',
16 13, 'CREATE SEQUENCE',
17 14, 'ALTER SEQUENCE',
18 15, 'ALTER TABLE',
19 16, 'DROP SEQUENCE',
20 17, 'GRANT',
21 18, 'REVOKE',
22 19, 'CREATE SYNONYM',
23 20, 'DROP SYNONYM',
24 21, 'CREATE VIEW',
25 22, 'DROP VIEW',
26 23, 'VALIDATE INDEX',
27 24, 'CREATE PROCEDURE',
28 25, 'ALTER PROCEDURE',
29 26, 'LOCK TABLE',
30 27, 'NO OPERATION',
31 28, 'RENAME',
32 29, 'COMMENT',
33 30, 'AUDIT',
34 31, 'NOAUDIT',
35 32, 'CREATE DATABASE LINK',
36 33, 'DROP DATABASE LINK',
37 34, 'CREATE DATABASE',
38 35, 'ALTER DATABASE',
39 36, 'CREATE ROLLBACK SEGMENT',
40 37, 'ALTER ROLLBACK SEGMENT',
41 38, 'DROP ROLLBACK SEGMENT',
42 39, 'CREATE TABLESPACE',
43 40, 'ALTER TABLESPACE',
44 41, 'DROP TABLESPACE',
45 42, 'ALTER SESSION',
46 43, 'ALTER USER',
47 44, 'COMMIT',
48 45, 'ROLLBACK',
49 46, 'SAVEPOINT',
50 47, 'PL/SQL EXECUTE',
51 48, 'SET TRANSACTION',
52 49, 'ALTER SYSTEM SWITCH LOG',
53 50, 'EXPLAIN',
54 51, 'CREATE USER',
55 52, 'CREATE ROLE',
56 53, 'DROP USER',
57 54, 'DROP ROLE',
58 55, 'SET ROLE',
59 56, 'CREATE SCHEMA',
60 57, 'CREATE CONTROL FILE',
61 58, 'ALTER TRACING',
62 59, 'CREATE TRIGGER',
63 60, 'ALTER TRIGGER',
64 61, 'DROP TRIGGER',
65 62, 'ANALYZE TABLE',
66 63, 'ANALYZE INDEX',
67 64, 'ANALYZE CLUSTER',
68 65, 'CREATE PROFILE',
69 67, 'DROP PROFILE',
70 68, 'ALTER PROFILE',
71 69, 'DROP PROCEDURE',
72 70, 'ALTER RESOURCE COST',
73 71, 'CREATE SNAPSHOT LOG',
74 72, 'ALTER SNAPSHOT LOG',
75 73, 'DROP SNAPSHOT LOG',
76 74, 'CREATE SNAPSHOT',
77 75, 'ALTER SNAPSHOT',
78 76, 'DROP SNAPSHOT',
79 79, 'ALTER ROLE',
80 85, 'TRUNCATE TABLE',
81 86, 'TRUNCATE CLUSTER',
82 88, 'ALTER VIEW',
83 91, 'CREATE FUNCTION',
84 92, 'ALTER FUNCTION',
85 93, 'DROP FUNCTION',
86 94, 'CREATE PACKAGE',
87 95, 'ALTER PACKAGE',
88 96, 'DROP PACKAGE',
89 97, 'CREATE PACKAGE BODY',
90 98, 'ALTER PACKAGE BODY',
91 99, 'DROP PACKAGE BODY'
92 ) "Command", sid, BLOCKING_SESSION WAITING_4, s.program
93 from v$session s, v$process p
94 where s.username is not null
95 and p.addr = s.paddr
96* order by 1, 5, 4
thank you
Gene Gurevich
<krish.hariharan@
quasardb.com>
To
12/20/2007 08:07 <genegurevich@xxxxxxxxxxxx>,
PM "'oracle-l'"
<oracle-l@xxxxxxxxxxxxx>
cc
Please respond to
<krish.hariharan@ Subject
quasardb.com> RE: Do selects block truncates?
Perhaps, what I am about to say has already been considered and ruled out.
That said, how was the conclusion drawn that the statement that is in
execution is the statement that is blocking the other two. If this has not
been ruled out then I would try to ensure that no other DML is pending form
the "other" session (ASH, v$transaction, etc).
I tend to look for the simpler explanation first before I take the red pill
to find out how deep the rabbit hole is.
-Krish
--
//www.freelists.org/webpage/oracle-l
Other related posts:
