删除一个tablespace soedata，发现db hang住了。
drop tablespace soedata including contents and datafiles;

但是数据库可以连接。
SQL> select username,sid,serial#,status,final_blocking_session,seq#,event from v$session where username = 'SYS';
 
   SID    SERIAL#  USERNAME   STATUS   FINAL_BLOCKING_SESSION       SEQ# EVENT                             
------ ----------  ---------- -------- ---------------------- ---------- -----------------------------------
     4          3  SYS        ACTIVE                                     class slave wait           
   195        153  SYS        ACTIVE                                     SQL*Net message to client  
   580         49  SYS        INACTIVE                                   SQL*Net message from client
   960        989  SYS        ACTIVE            1                    580 enq: TT - contention   
  1344         25  SYS        INACTIVE                                   SQL*Net message from client

等待的事件是enq: TT - contention。查查metalink都说是bug。

enq: TT - contention： Serializes DDL operations on tablespaces

做个systemstate 3
SQL> conn / as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in pft11g_ora_3238.trc

Processing Oradebug command 'setmypid'

*** 2011-07-25 13:24:24.006
Oradebug command 'setmypid' console output:

*** 2011-07-25 13:24:28.894
Processing Oradebug command 'unlimit'

*** 2011-07-25 13:24:28.894
Oradebug command 'unlimit' console output:

*** 2011-07-25 13:24:33.456
Processing Oradebug command 'hanganalyze 3'

*** 2011-07-25 13:24:34.454
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): dbs101.dbs101
  oradebug_node_dump_level: 3
  analysis initiated by oradebug
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 13:24:33 ]
      NOTE: scheduling delay has not been sampled for 0.768075 secs    0.000000
secs from [ 13:24:29 - 13:24:34 ], 5 sec avg
    0.000000 secs from [ 13:23:35 - 13:24:34 ], 1 min avg
    0.000000 secs from [ 13:19:35 - 13:24:34 ], 5 min avg
===============================================================================
 
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TT - contention'
     Chain 1 Signature Hash: 0xdc7ddbcc
 
===============================================================================
Non-intersecting chains:
 
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (dbs101.dbs101)
                   os id: 19865
              process id: 29, oracle@dbs101.dbs.com
              session id: 960
        session serial #: 989
    }
    is waiting for 'enq: TT - contention' with wait info:
    {
                      p1: 'name|mode'=0x54540006
                      p2: 'tablespace ID'=0x9
                      p3: 'operation'=0x0
            time in wait: 70 min 18 sec
           timeout after: never
                 wait id: 415
                blocking: 0 sessions
             current sql:
             short stack: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()
+112<-__sighandler()<-semtimedop()+12<-skgpwwait()+160<-ksliwat()+1865<-kslwaitc
tx()+163<-ksqcmi()+2956<-ksqgtlctx()+3810<-ksqgelctx()+561<-dtsdrv()+498<-opiexe
()+18796<-opiosq0()+3993<-kpooprx()+274<-kpoal8()+800<-opiodr()+910<-ttcpip()+22
89<-opitsk()+1670<-opiino()+966<-opiodr()+910<-opidrv()+570<-sou2o()+103<-opimai
_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+219
            wait history:
              * time between current wait and wait #1: 0.000176 sec
              1.       event: 'db file sequential read'
                 time waited: 0.008700 sec
                     wait id: 414             p1: 'file#'=0x1
                                              p2: 'block#'=0x1e11
                                              p3: 'blocks'=0x1
              * time between wait #1 and #2: 0.206371 sec
              2.       event: 'SQL*Net message from client'
                 time waited: 0.000754 sec
                     wait id: 413             p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000045 sec
              3.       event: 'SQL*Net message to client'
                 time waited: 0.000002 sec
                     wait id: 412             p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (dbs101.dbs101)
                   os id: 9486
              process id: 27, oracle@dbs101.dbs.com
              session id: 580
        session serial #: 49
    }
    which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x54435000
                      p2: '#bytes'=0x1
            time in wait: 2 min 5 sec
           timeout after: never
                 wait id: 704
                blocking: 1 session
             current sql:
             short stack: ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()
+112<-__sighandler()<-__read()+16<-nttfprd()+333<-nsbasic_brc()+338<-nsbrecv()+6
9<-nioqrc()+485<-__PGOSF36_opikndf2()+978<-opitsk()+850<-opiino()+966<-opiodr()+
910<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<
-__libc_start_main()+219
            wait history:
              * time between current wait and wait #1: 0.000044 sec
              1.       event: 'SQL*Net message to client'
                 time waited: 0.000003 sec
                     wait id: 703             p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000330 sec
              2.       event: 'SQL*Net message from client'
                 time waited: 5 min 9 sec
                     wait id: 702             p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000027 sec
              3.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 701             p1: 'driver id'=0x54435000
                                              p2: '#bytes'=0x1
    }
 
Chain 1 Signature: 'SQL*Net message from client'<='enq: TT - contention'
Chain 1 Signature Hash: 0xdc7ddbcc
-------------------------------------------------------------------------------

从dump文件中看出，session 960在等待session 580。
在session 580中曾经执行过下列语句：
select segment_name,min(file_id), min(block_id), sum(blocks)
from dba_extents
where segment_name in ('TESTWRITE1','TESTWRITE2','TESTWRITE3','TESTWRITE4')
group by segment_name;

只能猜测是查询表空间中的对象引起的。关闭session 580，enq: TT - contention等待就消
失。tablespace可以被成功删除。
SQL> drop tablespace soedata including contents and datafiles;
 
Tablespace dropped

注释：systemstate的dump还是清晰地显示出等待的session。