RE: Do selects block truncates?

From: <genegurevich_at_discover.com>
Date: Fri, 21 Dec 2007 09:44:18 -0600
Message-ID: <OF51CA66BB.B4723F00-ON862573B8.00566DD3-862573B8.0056743F@discover.com>

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_at_discover.com>,        
             PM                        "'oracle-l'"                        
                                       <oracle-l_at_freelists.org>            
                                                                        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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 21 2007 - 09:44:18 CST