无法删除临时表，10046跟踪发现异常？？？

ZALBB

*** 2008-04-09 17:10:26.787
*** ACTION NAME) 2008-04-09 17:10:26.740
*** MODULE NAMESQL*Plus) 2008-04-09 17:10:26.740
*** SERVICE NAMESYS$USERS) 2008-04-09 17:10:26.740
*** SESSION ID179.15770) 2008-04-09 17:10:26.740
ksqgtl *** MD-0029e994-00000000 mode=6 flags=0x401 timeout=5 ***
ksqgtl: xcb=0x00000000B1A88E30, ktcdix=2147483647, topxcb=0x00000000B1A88E30
        ktcipt(topxcb)=0x0
ksucti: init both txn DID and session DID from new DID: 0001-00D6-0000C1E7
ksqgtl:
        ksqlkdid: 0001-00D6-0000C1E7
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-00D6-0000C1E7
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-00D6-0000C1E7
ksqgtl: RETURNS 0
ksqgtl *** TM-00000239-00000000 mode=3 flags=0x401 timeout=21474836 ***
ksqgtl: xcb=0x00000000B1A88E30, ktcdix=2147483647, topxcb=0x00000000B1A88E30
        ktcipt(topxcb)=0x0
ksucti: init session DID from txn DID: 0001-00D6-0000C1E7
ksqgtl:
        ksqlkdid: 0001-00D6-0000C1E7
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-00D6-0000C1E7
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-00D6-0000C1E7
ksqgtl: RETURNS 0
ksqgtl *** TO-0029e994-00000001 mode=6 flags=0x401 timeout=0 ***
ksqgtl: xcb=0x00000000B1A88E30, ktcdix=2147483647, topxcb=0x00000000B1A88E30
        ktcipt(topxcb)=0x0
ksucti: init session DID from txn DID: 0001-00D6-0000C1E7
ksqgtl:
        ksqlkdid: 0001-00D6-0000C1E7
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-00D6-0000C1E7
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-00D6-0000C1E7
ksqcmi: TO,29e994,1 mode=6 timeout=0
ksqcmi: returns 51
ksqgtl: RETURNS 51
ksqrcl: TM,239,0
ksqcmi: TM,239,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0
ksqrcl: MD,29e994,0
ksqcmi: MD,29e994,0 mode=0 timeout=0
ksqcmi: returns 0
ksqrcl: returns 0

ZALBB

29e994　就是那个被删除的表的OBJECT_ID．

五“宅”一生

关注中。期待结果！

ZALBB

傻了一回，应该是到V$LOCK 里查找时候有否表的锁，我之前到V$LOCKED_OBJECT，难怪找不到．

刚才这样尝试：

[php]
NCBI@bi>drop table cb12409e1bcf14b23bf9d05005c7af;
drop table cb12409e1bcf14b23bf9d05005c7af
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


Elapsed: 00:00:00.10
NCBI@bi>SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME=UPPER('cb12409e1bcf14b23bf9d05005c7af');

OBJECT_ID
----------
   2790953

Elapsed: 00:00:00.09
NCBI@bi>select * from v$lock where id1=2790953;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000B11841F8 00000000B1184218    47 TO    2790953          1          3          0        684          0

Elapsed: 00:00:00.12
NCBI@bi>select machine, last_call_et, status, terminal from v$session where sid=47;

MACHINE              LAST_CALL_ET STATUS   TERMINAL
-------------------- ------------ -------- ----------------
DOMAINNC-NODE-4               66 INACTIVE NC-NODE-4

Elapsed: 00:00:00.09
NCBI@bi>select * from v$lock where id1=2790953;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000B11841F8 00000000B1184218    47 TO    2790953          1          3          0       1481          0

[/php]
忘了ORACLE 文档哪里介绍各种类型的锁了．如：TO, TM, TX等

棉花糖ONE

TO,没记录,reference里的v$lock有enqueue的说明

shiri512003

SQL> select name,description from v$Lock_type where type='TO';                     

NAME
----------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
Temp Object
Synchronizes DDL and DML operations on a temp object