删除在用临时表

临时表定义如下

CREATE GLOBAL TEMPORARY TABLE TEST_TEMP(ID NUMBER)
ON COMMIT PRESERVE ROWS

删除时报错

SQL> DROP TABLE TEST_TEMP
  2  /
DROP TABLE TEST_TEMP
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

查找使用此表的session

语句来自:http://www.itpub.net/forum.php?mod=viewthread&tid=1808996&extra=page%3D7%26filter%3Dtypeid%26typeid%3D37%26typeid%3D37

SELECT A.SID, C.SERIAL#, C.USERNAME, A.TYPE, A.LMODE
  FROM V$LOCK A, DBA_OBJECTS B, V$SESSION C
 WHERE A.ID1 = B.OBJECT_ID
   AND A.TYPE = 'TO'
   AND B.OBJECT_NAME = UPPER('TEST_TEMP')
   AND A.SID = C.SID

kill session 后再drop

SQL> ALTER SYSTEM KILL SESSION '24,43'
  2  /
System altered
SQL> DROP TABLE TEST_TEMP
  2  /
Table dropped