oracle 试图访问已经在使用的事物处理临时表,ORA-14450: 试图访问已经在使用的事务处理临时表（原创）...

--解决方法

--1、查找sessionID

select SID,SERIAL# from V$session where SID in

(select sid from v$enqueue_lock t where t.type='TO')

SID SERIAL#

-------------------------

103 257

113 181

124 89

126 632

--2、kill session

select 'alter system kill session '''||a.sid||','||a.SERIAL#||''';'

from V$session a where SID in (SELECT SID FROM

v$enqueue_lock t WHERE t.type='TO');

alter system kill session '103,257';

alter system kill session '113,181';

alter system kill session '124,89';

alter system kill session '126,632';

---------------------

select * from v$enqueue_lock t where t.type='TO'

select * from v$lock_type where type in ('AE','TO');

2.

可以从V$ACCESS视图里获取使用该临时表的SID。

ORACLE 10205

1 窗口1

SCOTT@portal>create

global temporary table SALGRADE_tmp as select * from SALGRADE;

Table created.

Elapsed: 00:00:00.07

SCOTT@portal>select

count(*) from SALGRADE_tmp;

COUNT(*)

----------------

0

Elapsed: 00:00:00.00

2 窗口2

SCOTT@portal>select *

from v$access where object='SALGRADE_TMP';

no rows selected

Elapsed: 00:00:00.42

3 窗口1

SCOTT@portal>insert

into SALGRADE_TMP select * from SALGRADE;

5 rows created.

Elapsed: 00:00:00.01

4 窗口2

SCOTT@portal>select *

from v$access where object='SALGRADE_TMP';

SID

OWNER OBJECT TYPE

----- ---------- ------------------------------

------------------------

604

SCOTT SALGRADE_TMP TABLE

Elapsed: 00:00:00.40

5 窗口1

SCOTT@portal>select

sid from v$mystat where rownum = 1;

SID

-----

604

第二种方法(原创)

select * from Gv$enqueue_lock t,dba_objects tt where

type''TO'

and t.id1=tt.object_id

and object_name='需要查询的表名'；

根据上面查到SID

select * from gv$session where SID='';

alter system kill session'sid,SERIAL#,@6'

---上面@6为实例，因为一个库由多个实例构成。指定实例以免误杀。