17,377
社区成员
发帖
与我相关
我的任务
分享
--首先模拟环境
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>
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>