Oracle锁表查看被锁的表和解锁

1、快速查出并结束被锁对象和会话，并处理

 --oracle当前的被锁对象
SELECT l.session_id sid,
       s.serial#,
       l.locked_mode 锁模式,
       l.oracle_username 登录用户,
       l.os_user_name 登录机器用户名,
       s.machine 机器名,
       s.terminal 终端用户名,
       o.object_name 被锁对象名,
       s.logon_time 登录数据库时间,s.SQL_ID,s.PREV_SQL_ID,
       --vsql.SQL_TEXT 执行的语句,
       'alter system kill session '||''''||s.SID||','||s.SERIAL#||''''||';' 结束被锁的会话进程
FROM v$locked_object l, all_objects o, v$session s,v$sql vsql
WHERE l.object_id = o.object_id
   AND l.session_id = s.sid
   AND　s.SQL_ID　＝　vsql.sql_id
   ORDER BY sid, s.serial#;

2、通过SQL_ID查询锁是执行什么SQL产生的

SELECT
SQL_TEXT, SQL_FULLTEXT, SQL_ID, LOADS, FIRST_LOAD_TIME,
PLSQL_EXEC_TIME, JAVA_EXEC_TIME, ROWS_PROCESSED, COMMAND_TYPE,
PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME,
KEPT_VERSIONS, ADDRESS, SERVICE, MODULE, MODULE_HASH,
ACTION, CPU_TIME, ELAPSED_TIME, OUTLINE_SID, CHILD_ADDRESS,
SQLTYPE, REMOTE, OBJECT_STATUS, LITERAL_HASH_VALUE, LAST_LOAD_TIME,
PROGRAM_ID, PROGRAM_LINE#,LAST_ACTIVE_TIME
FROM V$SQL
WHERE
SQL_ID = 'fpgcd35z6gru3'

3、相关表详解

--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

--查看被锁的表 
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

--查看哪个用户那个进程造成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

--查看连接的进程 
SELECT sid, serial#, username, osuser FROM v$session;

/*这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。*/
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

--杀掉进程 sid,serial#
alter system kill session'210,11562';