Oracle数据库- 11g 查询并终止锁定会话临时表的会话

在Oracle 11g中，要查询并终止锁定会话临时表(ON COMMIT PRESERVE ROWS)的会话，请按照以下步骤操作：

1. 查询锁定会话临时表的会话

方法1：使用VLOCK和VSESSION视图

SELECT l.sid, s.serial#, s.username, s.osuser, 
       s.machine, s.program, s.module, s.action,
       l.type, l.lmode, l.request, l.block
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
WHERE l.type = 'TO'  -- TO表示临时对象锁
AND l.id1 IN (
    SELECT object_id 
    FROM dba_objects 
    WHERE object_name = UPPER('您的表名') 
    AND owner = UPPER('表所有者')
    AND temporary = 'Y'
);

方法2：使用V$ACCESS视图（适用于11g）

SELECT s.sid, s.serial#, s.username, s.status,
       s.machine, s.program, s.logon_time
FROM v$access a
JOIN v$session s ON a.sid = s.sid
WHERE a.object = UPPER('您的表名')
AND a.owner = UPPER('表所有者');

2. 终止锁定会话

找到锁定会话的SID和SERIAL#后，使用以下命令终止会话：

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

3. 验证锁是否已释放

SELECT * FROM v$lock 
WHERE type = 'TO' 
AND id1 IN (
    SELECT object_id 
    FROM dba_objects 
    WHERE object_name = UPPER('您的表名') 
    AND owner = UPPER('表所有者')
);

4. 如果仍有问题，可尝试强制清理

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH SHARED_POOL;

5. 特殊情况处理（如果会话无法终止）

如果会话无法正常终止，可以尝试以下方法：

1. 在操作系统层面终止进程（需要DBA权限）：

SELECT s.sid, s.serial#, p.spid, s.osuser, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = [问题会话的SID];

然后使用操作系统命令：

kill -9 [SPID]

注意事项

1. 在Oracle 11g中，会话临时表的锁会在会话结束时自动释放
2. 终止会话前，确保该会话没有正在执行重要事务
3. 如果应用使用连接池，可能需要重启应用服务才能完全释放所有锁
4. 建议在非业务高峰期执行这些操作

预防措施

1. 为临时表使用单独的表空间
2. 设置合理的会话超时参数（如PROFILE中的IDLE_TIME）
3. 定期监控长时间运行的会话：

SELECT sid, serial#, username, program, status, 
       last_call_et/3600 as hours_active
FROM v$session
WHERE type = 'USER'
ORDER BY last_call_et DESC;