-- 查看与此临时表相关的会话,并Kill掉!-- 操作如下 : --查询所有的死锁: SELECT * FROM V$LOCK; --查询所有的会话: SELECT * FROM V$SESSION; --查询所有的死锁的会话: SELECT SN.SID, SN.SERIAL#, SN.USERNAME, SN.MACHINE, SN.TERMINAL, SN.PROGRAM, SN.TYPE FROM V$SESSION SN WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); ------------------------------------------------------------ SELECT 'ALTER SYSTEM KILL SESSION '''||SN.SID||','||SN.SERIAL#||''';' FROM V$SESSION SN WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); ALTER SYSTEM KILL SESSION '1266,3834'; ALTER SYSTEM KILL SESSION '1282,2303'; ALTER SYSTEM KILL SESSION '1494,1'; ALTER SYSTEM KILL SESSION '1495,1'; ALTER SYSTEM KILL SESSION '1496,1'; ALTER SYSTEM KILL SESSION '1497,1';--杀掉死锁会话: ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; ALTER SYSTEM KILL SESSION '1323,13189'; ALTER SYSTEM KILL SESSION '1350,27497'; ALTER SYSTEM KILL SESSION '1367,13850'; ALTER SYSTEM KILL SESSION '1369,14223'; ALTER SYSTEM KILL SESSION '1440,6115'; ALTER SYSTEM KILL SESSION '1469,20601'; ALTER SYSTEM KILL SESSION '1478,21239'; ALTER SYSTEM KILL SESSION '1494,1'; ALTER SYSTEM KILL SESSION '1495,1'; ALTER SYSTEM KILL SESSION '1496,1'; ALTER SYSTEM KILL SESSION '1497,1';
-- 然后重新登录,再执行Drop临时表操作试试!-- 不过:在删除任何对象之前,最好先查看一下对象相关性(是否有存储过程、函数、触发器引用了该对象)-- SQL类似于下: select name, type, referenced_name, referenced_type from user_dependencies where referenced_name='TJ2_HLL_USERS_BAK';
--查询所有的死锁:
SELECT * FROM V$LOCK;
--查询所有的会话:
SELECT * FROM V$SESSION;
--查询所有的死锁的会话:
SELECT SN.SID,
SN.SERIAL#,
SN.USERNAME,
SN.MACHINE,
SN.TERMINAL,
SN.PROGRAM,
SN.TYPE
FROM V$SESSION SN
WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); ------------------------------------------------------------
SELECT 'ALTER SYSTEM KILL SESSION '''||SN.SID||','||SN.SERIAL#||''';'
FROM V$SESSION SN
WHERE SN.SID IN (SELECT LK.SID FROM V$LOCK LK); ALTER SYSTEM KILL SESSION '1266,3834';
ALTER SYSTEM KILL SESSION '1282,2303';
ALTER SYSTEM KILL SESSION '1494,1';
ALTER SYSTEM KILL SESSION '1495,1';
ALTER SYSTEM KILL SESSION '1496,1';
ALTER SYSTEM KILL SESSION '1497,1';--杀掉死锁会话:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; ALTER SYSTEM KILL SESSION '1323,13189';
ALTER SYSTEM KILL SESSION '1350,27497';
ALTER SYSTEM KILL SESSION '1367,13850';
ALTER SYSTEM KILL SESSION '1369,14223';
ALTER SYSTEM KILL SESSION '1440,6115';
ALTER SYSTEM KILL SESSION '1469,20601';
ALTER SYSTEM KILL SESSION '1478,21239';
ALTER SYSTEM KILL SESSION '1494,1';
ALTER SYSTEM KILL SESSION '1495,1';
ALTER SYSTEM KILL SESSION '1496,1';
ALTER SYSTEM KILL SESSION '1497,1';
select name, type,
referenced_name, referenced_type
from user_dependencies
where referenced_name='TJ2_HLL_USERS_BAK';
从而导致drop 全局会话级 临时表,才会出现上述提示,
为什么呢??
从而导致drop 全局会话级 临时表,才会出现上述提示,
为什么呢??
oracle临时表实际上是数据“临时”,而表定义并非”临时“,
所以不要一使用完后就DROP,使用时又创建,这是使用oracle临时表的误区。
搞不懂,为什么,修改表结构时,就是改不了啊
DROP TABLE 表名;--再删除事务级需要先commit 再删除