| 观察drop partition时的锁情况 | DBA之路：OoNiceDream |

观察drop partition时的锁情况

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/02/lock-when-drop-partition.html

借一次drop partition机会，观察下相关表及分区的锁情况。
由于表较大，便于观察，作个简单的记录,供以后参考（相关表名等信息略作更改）：

会话一,记录会话信息，进行DROP操作：

SQL> select a.spid,b.sid,b.serial# from v$process a,v$session b
  2  where a.addr=b.paddr and b.sid=61;
 
SPID                SID    SERIAL#
------------ ---------- ----------
21011                61      13765
 
SQL> alter table USER01.DB_USE_TABLE_PAR drop partition DB_USE_TABLE_2008_05 update global indexes;

会话二，查看锁情况：

SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid=61;
 
       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        61 TX    3276831      15377          6          0        295          0
        61 TM       6507          0          6          0        295          0
        61 TM       6303          0          3          0        295          0
 
SQL> 
SQL> select owner, object_name, subobject_name, object_id, object_type
  2  from dba_objects
  3  where object_id in (6507, 6303);
 
OWNER      OBJECT_NAME          SUBOBJECT_NAME         OBJECT_ID OBJECT_TYPE
---------- -------------------- --------------------- ---------- ------------------
USER01     DB_USE_TABLE_PAR                                 6303 TABLE
USER01     DB_USE_TABLE_PAR     DB_USE_TABLE_2008_05        6507 TABLE PARTITION
 
SQL> select sid type,id1,id2,lmode,request,ctime,block
  2  from v$lock where sid in(
  3  select sid
  4    from v$lock
  5   where sid <>61 and id1=6303) ;
 
      TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
        40    4063251      12501          6          0         13          0
        40       6684          0          3          0         13          0
        40       6303          0          3          0         13          0
       174    2818054      17703          6          0         16          0
       174       6684          0          2          0         16          0
       174       6303          0          2          0         16          0
 
6 rows selected.

可以看出，在表DB_USE_TABLE_PAR上加的是MODE为3的锁，在分区DB_USE_TABLE_2008_05上加的是MODE为6的锁，并不阻碍数据插入到别的其它分区。同时也不影响相关索引的正常使用：

SQL> set autotrace on    
SQL> select USE_TABLE_NUM
  2  from USER01.DB_USE_TABLE_PAR partition(DB_USE_TABLE_2008_05)
  3  where USE_TABLE_NUM=20317005;
 
USE_TABLE_NUM
-------------
     20317005
 
 
Execution Plan
----------------------------------------------------------
  0     SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=413219 Bytes=5371847)
  1   0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'DB_USE_TABLE_PAR' (Cost=3 Card=413219 Bytes=5371847)
  2   1     INDEX (RANGE SCAN) OF 'DB_USE_TABLE_PAR_INDEX1' (NON-UNIQUE) (Cost=3 Card=165288)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          4  physical reads
        144  redo size
        499  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> select owner,index_name,index_type,partitioned,status from dba_indexes
  2  where index_name='DB_USE_TABLE_PAR_INDEX1';
 
OWNER-   INDEX_NAME                  INDEX_TYPE     PAR STATUS
-------- --------------------------- -------------- --- --------
USER01   DB_USE_TABLE_PAR_INDEX1     NORMAL         NO  VALID