Oracle锁表查询和解锁

1.以下为相关表

v$lock、v$sqlarea、v$session、v$process、v$locked_object、all_objects、v$session_wait.

2.通过管理查询被锁表的相关信息

select b.sql_id,b.username,b.sid,b.serial#,c.object_name,b.OSUSER,b.MACHINE,b.PROGRAM,b.LOGON_TIME,b.COMMAND,b.LOCKWAIT,b.SADDR,b.PADDR,b.TADDR,b.SQL_ADDRESS,a.LOCKED_MODE 
from v$locked_object a, v$session b, dba_objects c where a.session_id = b.sid and a.object_id = c.object_id order by b.logon_time;

通过语句可以发现用到了上面所给出的相关表

v$locked_object、v$session、dba_objects

3.查找出是那条SQL语句导致了锁表

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 
或
select * from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

4.通过sql_id查找是执行这条sql语句的机器名

select username,status,schemaname,osuser,machine,module,action,logon_time,event from v$session where schemaname<>'SYS' and machine in ( 
select machine from v$active_session_history where sql_id in (select sql_id from v$sql where sql_text like '%update%'));

或

select b.sql_id,username,status,schemaname,osuser,a.machine,module,action,logon_time,event from v$session a left join 
( select sql_id,machine from v$active_session_history where sql_id in (select sql_id from v$sql where sql_text like '%update%') ) b on 
a.machine=b.machine where schemaname<>'SYS' 

5.通过拥有DBA权限的用户解除数据库中锁住的表（需要用到sid和serial），执行下面的与语句即可解锁：

alter system kill session 'sid,serial#';

6.查询某session正在执行的语句，从而可以快速定位一直进行的事物

select /*+ ORDERED */ sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.sid = '143')  /* 此处143 为SID*/ ORDER BY piece ASC;