[求助]关于 oracle 临时表 ORA-14450,如何解除所有会话的数据绑定

Question

本帖最后由 BubbleKitty 于 2010-08-24 16:34:35 编辑

Answer 1

由于不知道有哪些会话session正在使用這個临时表，無法解除臨時临时表的数据绑定，也就不能修改和刪除這個临时表，請幫忙看看

Answer 2

临时表不是对每个session是不相关的么,是当前session吧

Answer 3

是的，每個session是不相關的，那是相對於客戶端我現在要對臨時表的結构進行調整，由於不知道有哪些客戶端正在使用，無法解除臨時表的數據綁定，不能修改表結构。。

Answer 4

SELECT * FROM v$enqueue_lock t WHERE t.type='TO';
把对应的session kill掉试试.
本机试验是成功的.

Answer 5

俺就是想知道有哪些session正在使用这个临时表。。
不知道session id，怎么杀啊？
本机当然没问题。

Answer 6

正在使用中的数据库有成千上万个session,明天一大早再试试alter

Answer 7

--首先模拟环境
SQL> CREATE GLOBAL TEMPORARY TABLE t_tmp_t1
  2  (a NUMBER(2));
 
Table created
 
SQL> 
SQL> INSERT INTO t_tmp_t1
  2  VALUES(1);
 
1 row inserted
 
SQL> 
--新开session测试
SQL> ALTER TABLE t_tmp_t1 ADD c NUMBER(2)  ;
 
ALTER TABLE t_tmp_t1 ADD c NUMBER(2)
 
ORA-14450: attempt to access a transactional temp table already in USE
--查找sid及serial#
SQL> SELECT DISTINCT a.sid,a.SERIAL# FROM v$session a,v$sql b,v$enqueue_lock c
  2   WHERE a.SID=c.SID AND c.TYPE='TO' AND a.MODULE_HASH=b.MODULE_HASH AND lower(b.SQL_TEXT) LIKE '%t_tmp_t1%';
 
    SID      SERIAL#
---------- ----------
    129        144    
    
--使用sys用户登陆
SQL> alter system kill session '129,144'; 
 
System altered
 
SQL> 
--回到scott
SQL> ALTER TABLE t_tmp_t1 ADD c NUMBER(2)  ;
 
Table altered
 
SQL> desc t_tmp_t1
Name Type      Nullable Default Comments 
---- --------- -------- ------- -------- 
A    NUMBER(2) Y                         
C    NUMBER(2) Y                         
 
SQL> 

Answer 8

多謝樓上熱心詳細的解答。。查找sid及serial#你用到v$enqueue_lock：這個是enqueue對象所有的鎖
用戶在使用這個臨時表，這個臨時表不會被鎖啊，所以查到的數據是空的。。

Answer 9

临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，临时表不需要DML锁.当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.

Answer 10

找到TO的定义了.感觉上面的应该能解决啊,你查找sid的时候查找的表名改没?
SQL> SELECT * FROM v$lock_type t WHERE t.TYPE='TO';
 
TYPE                                                             NAME                                                             ID1_TAG                                                          ID2_TAG                                                          IS_USER DESCRIPTION
---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------- --------------------------------------------------------------------------------
TO                                                               Temp Object                                                      object #                                                         1                                                                NO      Synchronizes DDL and DML operations on a temp object
 
SQL> 

Answer 11

TO锁就是对临时表的一种专用锁，不过baidu，google啥的真不容易查找相关信息。
http://comp.mailarchive.ca/databases.oracle.server/2003-06/1767.html

Answer 12

V$LOCK_TYPE is a good starting point!SQL> select type,name,description from v$lock_type where type in ('AE',
'TO');TYPE NAME DESCRIPTION
---- --------------------
------------------------------------------------------------
AE Edition Lock Prevent Dropping an edition in use
TO Temp Object Synchronizes DDL and DML operations on a temp
objectAE is about application code versioning (editions) which should be released
as part of Oracle 11.2 I think. 
TO is about temp tables so someone wouldn't be able to drop or alter the
structure of the table def while there's some other session having data in
the temp tab.--
Tanel Poder
http://blog.tanelpoder.com