Oracle中查看一个表是否被锁住

 

 

                                                     

Oracle中查看一个表是否被锁住

 

 

SELECT OBJECT_NAME,SESSION_ID SID,MACHINE,VS.MODULE, 'ALTER SYSTEM KILL SESSION '''||SESSION_ID|| ', '||SERIAL#|| '''; ' KILL_SESSION,VS.STATUS,VS.ACTION,SERIAL#,ORACLE_USERNAME,OS_USER_NAME
FROM V$LOCKED_OBJECT VO, V$SESSION VS, ALL_OBJECTS AO
WHERE VO.SESSION_ID = VS.SID AND AO.OBJECT_ID = VO.OBJECT_ID AND NVL(VS.ACTION, ' ') <> 'Service Management ' ORDER BY OBJECT_NAME,MACHINE,VS.MODULE;

 

 

 

这段代码 可以找到，非系统锁的表，并给出 KILL SESSION语句。可以查到是那台机器，有什么程序锁的表

 

 

 

--查出所有被锁住的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,

c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
--通过SID, SERIAL解锁
--alter system kill session 'SID, SERIAL';

 

 

 

oracle ORA-14452错误处理  临时表被锁
create global temporary table on commit preserve rows 用于会话相关,
也就在事务结束后truncate data in the temporary table,但如果在会话未结束时要

修改temporary table就会出现错误：
ORA-14452: attempt to create, alter or drop an index on temporary table

already in use
经查，该错误的解释为：
Cause: An attempt was made to create, alter or drop an index on temporary

table which is already in use.
Action: All the sessions using the session-specific temporary table have to

truncate table and all the transactions using transaction specific

temporary table have to end their transactions.

处理步骤：
1、先从user_objects中查询到该表的object_id:
select object_id from user_objects where object_name=upper

('TMP_365100930');

2、根据查到的object_id知道使用该表的session：
select * from v$lock where id1=&object_id;

3、在从v$session视图中查到该session的SID和SERIAL#：
select * from v$session where sid=181;

4、杀掉这些进程:
alter system kill session SID,SERIAL#;