查看oracle锁表的进程和表

select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode 
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 


select sess.sid, 
    sess.serial#, 
    lo.oracle_username, 
    lo.os_user_name, 
    ao.object_name, 
    lo.locked_mode ,
    'alter system kill session ' || chr(39) || sess.sid || ',' || sess.serial# || chr(39) || ';'
    from v$locked_object lo, 
    dba_objects ao, 
    v$session sess 

where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

查看历史SQL语句：

select * from v$active_session_history

查询Session中的SQL语句


SELECT VS.SQL_TEXT
  FROM V$SQL VS, V$SESSION SN
 WHERE VS.SQL_ID = SN.SQL_ID

   AND SN.SID = &SESSION_ID;

查询锁表、阻塞的SQL语句：


select s.MACHINE, s.PROGRAM, s.event, s.STATUS, q.sql_text
  from gv$session s, gv$sql q
where sid in (select distinct sid
                 from gv$lock
                where type in ('TX', 'TM')
                  and inst_id = s.inst_id)
   and nvl(s.sql_id, s.prev_sql_id) = q.sql_id
   and s.inst_id = q.inst_id;


SELECT 'alter system kill session ' || chr(39) || SID || ',' || SERIAL# || chr(39) || ';'SID, SERIAL# FROM GV$SESSION V   
  WHERE SID IN (SELECT SID FROM GV$LOCK  L, DBA_OBJECTS O   
         WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('INTE_FIN_SUB')   );  ---ALTER SYSTEM KILL SESSION '20,2661';  


--查询使用某个表的进程：
SELECT SID, SERIAL# FROM GV$SESSION V   
  WHERE SID IN (SELECT SID FROM GV$LOCK  L, DBA_OBJECTS O   
         WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('TEMP_FSF_GJXX')   ); 




kill rac模式上的实例进程
select * from DBA_OBJECTS O     WHERE O.OBJECT_NAME =UPPER('INTE_FIN_SUB');
select * from GV$LOCK  L where L.ID1 in 
(select O.OBJECT_ID from DBA_OBJECTS O     WHERE O.OBJECT_NAME =UPPER('INTE_FIN_SUB'));
         
select * from GV$SESSION V where V.SID in ('2138','1496','365');
alter system kill session '365,58073,@2' immediate;
alter system kill session '1496,133,@2' immediate;
alter system kill session '2138,4769,@2' immediate;

select * from v$asm_diskgroup

SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#    
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S   
WHERE L.OBJECT_ID = O.OBJECT_ID      AND L.SESSION_ID = S.SID;   




1.查询数据库中的锁


select * from v$lock;
select * from v$lock where block=1;


2.查询被锁的对象


select * from v$locked_object;


3.查询阻塞


查被阻塞的会话
select * from v$lock where lmode=0 and type in ('TM','TX');


查阻塞别的会话锁
select * from v$lock where lmode>0 and type in ('TM','TX');


4.查询数据库正在等待锁的进程


select * from v$session where lockwait is not null;


5.查询会话之间锁等待的关系


select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b
where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.block=0;


6.查询锁等待事件
select * from v$session_wait where event='enqueue';
7.查找锁住的表和解锁
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';






赋予用户加密权限


查看锁表进程SQL语句2： 
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 


杀掉锁表进程： 
如有記錄則表示有lock，記錄下SID和serial# ，將記錄的ID替換下面的738,1429，即可解除LOCK 
alter system kill session '738,1429'; 


步骤1：用以下SQL查看进程列表，判断出被锁定的表
 
SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID||', '||vss.SERIAL# 删除号,
 lo.locked_mode,lo.SESSION_ID, vss.SERIAL#,vss.action Action,vss.osuser OSUSER, vss.LOGON_TIME,
 vss.process AP_PID, VPS.SPID DB_PID ,vss.*
 From v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
 Where lo.OBJECT_ID = dob.OBJECT_ID
 and lo.SESSION_ID = vss.SID
 AND VSS.paddr = VPS.addr
 order by 2,3,DOB.object_name
 --查找被锁表
 
步骤2 删除进程，如之前的“删除号”查找出的结果为“286, 2184”，则运行以下SQL
 
ALTER system kill session '286, 2184'


--Oracle中删除正在连接的用户 
select s.username,s.osuser,s.program,s.MACHINE, s.sid, s.serial#, p.spid, 
      'alter system kill session '||''''||trim(s.sid)||','||trim(s.serial#)||''';'
from v$session s,v$process p
where s.paddr = p.addr   and s.username is not null; 
















今天在导一个临时表的数据，导出完成后准备清空数据，执行truncate命令时，遇到如下问题：




ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效




 解决方法如下：


=========================================================


SQL> select session_id from v$locked_object;


SESSION_ID
 ----------
        56


SQL> SELECT sid, serial#, username, osuser FROM v$session where sid = 142;


       SID    SERIAL# USERNAME                       OSUSER
 ---------- ---------- ------------------------------ ------------------------------
        56         2088 ghb                          fy


SQL> ALTER SYSTEM KILL SESSION '56,2088';


System altered


执行完上述命令后，提示会话断开。重新连接数据库，然后执行truncate操作，成功！