--查询被锁的表 

--查询被锁的表 
select A.sid, b.serial#, 
decode(A.type, 
    'MR', 'Media Recovery', 
    'RT','Redo Thread', 
    'UN','User Name', 
    'TX', 'Transaction', 
    'TM', 'DML', 
    'UL', 'PL/SQL User Lock', 
    'DX', 'Distributed Xaction', 
    'CF', 'Control File', 
    'IS', 'Instance State', 
    'FS', 'File Set', 
    'IR', 'Instance Recovery', 
    'ST', 'Disk Space Transaction', 
    'TS', 'Temp Segment', 
    'IV', 'Library Cache Invalida-tion', 
    'LS', 'Log Start or Switch', 
    'RW', 'Row Wait', 
    'SQ', 'Sequence Number', 
    'TE', 'Extend Table', 
    'TT', 'Temp Table', 
    'Unknown') LockType, 
c.object_name, 
b.username, 
b.osuser, 
decode(a.lmode,   0, 'None', 
            1, 'Null', 
            2, 'Row-S', 
            3, 'Row-X', 
            4, 'Share', 
            5, 'S/Row-X', 
            6, 'Exclusive', 'Unknown') LockMode, 
B.MACHINE,D.SPID 
from v$lock a,v$session b,all_objects c,V$PROCESS D 
where a.sid=b.sid and a.type in ('TM','TX') 
and c.object_id=a.id1 
AND B.PADDR=D.ADDR  ;

--行级锁

--行级锁
select /*+ rule */
 s.username,
 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', null) lock_level,
 o.owner,
 o.object_name,
 o.object_type,
 s.sid,
 s.serial#,
 s.terminal,
 s.machine,
 s.program,
 s.osuser
  from v$session s, v$lock l, dba_objects o
 where l.sid = s.sid
   and l.id1 = o.object_id(+)
   and s.username is not null ;

 

 --用于解锁 

alter system kill session 'SID,SERIAL#' ;
alter system kill session '4450,26459' ;

--分析表

analyze table xxxxx  compute statistics;

----查某session 正在执行的sql语句，从而可以快速定位到哪些操作或者代码导致事务一直进行没有结束等.
 

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 = '7')  /* 此处67 为SID*/
 ORDER BY piece ASC;