oracle锁表问题
1、Select * From v$locked_object;锁表对象
根据sessionid,到Tools--sessions找到相应的sessions即可看到锁表的sql语句

或直接执行：
         Select a.inst_id,
       c.sid,
       c.serial#,
       d.name,
       b.object_name,
       c.username,
       a.object_id,
       c.program,
       c.status,
       c.osuser,
       c.terminal
  from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
   and a.inst_id = c.inst_id(+)
   and a.session_id = c.sid(+)
   and c.command = d.action;

2、Select * From v$session_longops 锁表时间
Select * From dba_objects;

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Select * from dba_locks where blocking_others like 'Blocking%';

---
Select a.inst_id,
       c.sid,
       c.serial#,
       d.name,
       b.object_name,
       c.username,
       a.object_id,
       c.program,
       c.status,
       c.osuser,
       c.terminal
  from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
   and a.inst_id = c.inst_id(+)
   and a.session_id = c.sid(+)
   and c.command = d.action;


可以查看死锁的状态，死锁的用户，死锁的机器以及死锁的程序
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)

Select * From v$locked_object;
Select * From dba_objects;

如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC

解锁.c ____我只会解锁.


(1)以系统管理员登陆
sqlplus name/passwd@dbname as sysdba


(2)查看锁

select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;


(3)把锁给KILL掉
--alter system kill session 'sid,serial#';
alter system kill session '146,21177';

(4)给账户解锁
sqlplus sys/oracle1234@remotedb as sysdba
alter user hbhp account unlock;

===============================
处理Oracle中杀不掉的锁

一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。



1.下面的语句用来查询哪些对象被锁：


select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)

【注】以上两步，可以通过Oracle的管理控制台来执行。

3.如果利用上面的命令杀死一个进程后，进程状态被置为"k